Running Totals Over Date Periods

1. Understanding the Running Total

Before we write any code, let's be crystal clear on what a running total is. It's a cumulative sum that grows over time. For each new date, you add that date's value to the sum of all the preceding dates.

This is different from the Year-to-Date (YTD) calculations you learned earlier, as a running total does not reset at the end of the year.

To see a quick demonstration of this concept and how it differs from a YTD calculation, please watch the first minute of the following video from SQLBI.

2. The Core DAX Pattern for a Running Total

As the video mentions, the logic is to calculate our measure (e.g., Total Sales) for all dates that are less than or equal to the last date visible in the current filter context. This is where your understanding of CALCULATE and evaluation context becomes crucial.

Let's watch the rest of that video to see how this logic translates into a DAX measure.

The video demonstrates the classic pattern for a running total. Let's break it down, which aligns with your preference for understanding the theory before applying it.

Sales Running Total =
-- This measure calculates the cumulative sum of sales from the beginning of time
-- up to the last date visible in the current filter context.
VAR LastVisibleDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        [Total Sales],
        'Date'[Date] <= LastVisibleDate,
        ALL ( 'Date' )
    )

Here's how it works:

1. VAR LastVisibleDate = MAX ( 'Date'[Date] ): Just as in our previous lesson, this variable captures the last date in the current filter context. In a table with daily granularity, this will be the date of the current row.
2. CALCULATE ( [Total Sales], ... ): This begins the calculation, preparing to modify the context.
3. ALL ( 'Date' ): This is a critical step. It acts as a CALCULATE modifier that removes all existing filters from the Date table. This is necessary because, without it, the filter context from the visual's row would only let us "see" the current date. We need to remove this filter so we can evaluate sales across all dates.
4. 'Date'[Date] <= LastVisibleDate: This is the new filter we apply. After ALL('Date') has cleared the slate, this filter expression is evaluated. It tells CALCULATE to consider all dates in the entire Date table that are on or before our LastVisibleDate.

This combination of removing the initial filter and then applying a new, broader filter is a powerful and common pattern in DAX.

3. Application and a More Robust Version

Now, it's time for you to apply this. Using the pattern above, create the Sales Running Total measure in your Power BI project. Add it to a table visual alongside your date and [Total Sales] measure to verify that it accumulates correctly.

Once you have the basic version working, we can enhance it. A common requirement in professional reports is to prevent the running total from appearing for future dates where no sales have occurred. The basic pattern will continue to show the last known total for all future dates in your Date table, which can be misleading.

The article below from DAX Patterns presents a more robust version of the formula that solves this exact problem.

The enhanced pattern from the article introduces a "guard clause":

Sales Amount RT :=
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR FirstVisibleDate = MIN ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE (
        MAX ( 'Sales'[Order Date] ),
        REMOVEFILTERS () -- Modern equivalent of ALL()
    )
VAR Result =
    IF (
        FirstVisibleDate <= LastDateWithSales,
        CALCULATE (
            [Sales Amount],
            'Date'[Date] <= LastVisibleDate
        )
    )
RETURN
    Result

The key addition is the IF statement. It checks if the period currently being evaluated in the visual (FirstVisibleDate) is before the very last date a sale was ever made (LastDateWithSales). If not, it returns a blank, which prevents the running total from populating into the future. This is an excellent technique for keeping your reports clean.

Finally, to reinforce the generic nature of this pattern, please review the text-based version of the video you watched. It not only recaps the date-based running total but also shows how the exact same logic can be applied to a non-date dimension, like customer categories.

Conclusion

Congratulations on completing this module! You have now learned how to create both rolling totals and running totals—two of the most common and powerful analytical calculations in business intelligence.

Key Takeaways:

• A running total accumulates values from the beginning of time, unlike a rolling total (fixed window) or YTD (resets annually).
• The core DAX pattern for a running total is: CALCULATE([Measure], 'Date'[Date] <= MAX('Date'[Date]), ALL('Date')).
• The ALL('Date') function is essential for removing the initial filter context so you can apply a new filter across all dates.
• You can create more robust measures by adding guard clauses (e.g., using IF) to handle edge cases, like preventing calculations from appearing for future dates.
• This cumulative pattern is generic and can be applied to any sortable column, not just dates.

Preview of the Next Lesson:
In our next module, we will dive into creating more dynamic and interactive reports. We'll start by learning how to use the SELECTEDVALUE function to capture a user's selection from a slicer, allowing you to build measures that respond directly to user input.