Running totals are a major analytical feature because they help you see progressive growth or movement in your data over time, rather than just point-in-time values.
In this article we will look at two major types of Running Totals and how they can be calculated through DAX.
The two major types of running totals are :
Lets use a typical and a simple data model.
A Date table and a Sales Table mapped through the Date columns.
Sample Data :-
DateTable :
SalesTable:
Cumulative Running Total
First, we define a measure that returns the SUM of the sales values. Lets name it as SalesAmount
Sales Amount = SUM(SalesData[SalesValue])
Next, lets calculate a simple running total that accumulates all sales values across each date in the model and the running total that resets at the start of a new month.
This is pretty straightforward through the PowerBI DAX inbuilt DATESMTD function.
RunningTotal = CALCULATE(SalesData[Sales Amount],DATESMTD(DateTable[Date]))
As highlighted above in red, the running total resets at the start of a new month.
Incase, if there is no need for a value reset we can write a custom DAX query that accumulates all sales values for each date of the model.
RunningTotal =
VAR MaxDate = MAX(DateTable[Date])
VAR DateLessThanMaxDate = FILTER(ALL(DateTable),(DateTable[Date]<=MaxDate))
RETURN CALCULATE (SalesData[Sales Amount],DateLessThanMaxDate)
Unlike the DATESMTD function, as highlighted above the output of the measure did not reset at the start of a new month.
A brief walkthrough of the measure logic that calculates the above running total.
In the measure we declare two variables, the first variable sets the latest date in the current filter context and the second variable filters date values that are less than the current date of the filter context. The measure then returns the sum of sales amount based on the filtered date range. The output is a running total that spans across all the dates in the model that satisfy the sales amount.
Next, lets calculate the running total based for month of the year of the current filter context. This calculation will accumulate values month over month within each year, while resetting when a new year begins.
To achieve this, we can use the DAX inbuilt DATESYTD function.
RunningTotal = CALCULATE (SalesData[Sales Amount],DATESYTD(DateTable[Date]))
To achieve the same results without using the inbuilt DATESYTD function we can write a custom DAX measure. The logic is almost similar to the one we wrote earlier to calculate the running total over dates. The only difference being that we have replaced the date filters with Month and Year filter.
RunningTotal =
VAR MaxYear = MAX(DateTable[Year])
VAR MaxMonth =MAX(DateTable[Month])
VAR DateLessThanMaxDate = FILTER(ALL(DateTable),DateTable[Month]<=MaxMonth
&& DateTable[Year]=MaxYear)
RETURN CALCULATE (SalesData[Sales Amount],DateLessThanMaxDate)
To calculate running total over months that does not reset each year, the DAX query in the measure would be
RunningTotal =
VAR MaxYear = MAX (DateTable[Year])
VAR MaxDate =MAX(DateTable[Date])
VAR DateLessThanMaxDate = FILTER(ALL(DateTable), DateTable[Date]<=MaxDate && DateTable[Year]<=MaxYear)
RETURN CALCULATE (SalesData[Sales Amount],DateLessThanMaxDate)
Next, lets calculate a running total based on a Year i.e. running values accumulated across multiple years.
We use the same logic that was used to calculate the continuous running total across all months with the only difference being that we are now filtering only on the years of the current filter context.
We can achieve it using the following DAX measure.
RunningTotal =
VAR MaxYear = MAX(DateTable[Year])
VAR YearLessThanMaxDate = FILTER(ALL(DateTable), DateTable[Year] <= MaxYear)
RETURN CALCULATE (SalesData[Sales Amount], YearLessThanMaxDate)
đź’ˇ
There are obvious limitations when working with the inbuilt DATESYTD and DATESMTD and many other time intelligence functions. The major one being that these functions cannot be used in calculated columns for DirectQuery mode and also when using RLS . If your semantic model has such limitations you can use the custom DAX queries from this article.
Sliding/Moving Running Total
A Sliding Running Total(also called as Moving Cumulative Total) is a type of a running total which instead of accumulating from the very beginning of your data, it accumulates values only within a sliding (rolling) time window.
If you have good experience or knowledge with SQL Server you might be aware that sliding running total is achieved using a combination of built in windows function of PARTITION BY and defining a window frame through UNBOUNDED PRECEDING or UNBOUNDED FOLLOWING with respect to the CURRENT ROW. Also the LEAD/LAG functions can be very handy for such calculations.
Lets look at a typical example to illustrate the subtle difference between Cumulative Running Total and Sliding Running Total
Cumulative Running Total (Year-to-Date)
Sliding Running Total (last 3 months)
Jan = 10 (Jan)
Feb = 25 (Jan » 10 + Feb » 15)
Mar = 75 (Jan » 10 + Feb » 15 + Mar » 50)
Apr = 70 (Feb » 15 + Mar » 50 + Apr » 5 → Jan drops out)
May = 80 (Mar » 50 + Apr » 5 + May » 25 → Jan & Feb drops out)
Now that we understand Sliding Running Total, lets write a DAX measure to calculate the sliding running totals based on our data model.
In our measure we should ensure that the measure calculates the values within a sliding window for months of the same year without overlapping with the same months from past or future years.
To make it clearer and easy for explanation and understanding , lets start with a one month sliding window
RunningTotal =
VAR SlidingJump = 2
VAR MaxYear = MAX(DateTable[Year])
VAR MaxMonth = MAX(DateTable[Month])
VAR MonthLessThanMaxMonth =
FILTER (
ALL(DateTable),
DateTable[Month] <= MaxMonth
&& DateTable[Month] > MaxMonth - SlidingJump
&& DateTable[Year] = MaxYear
)
RETURN CALCULATE ( SalesData[Sales Amount], MonthLessThanMaxMonth)
In the above measure, we have set the sliding variable SlidingJump value to 2, which means we take into consideration the current month and previous month and display the sliding total against the current month of the filter context.
We set two variables that store the Year and Month values in the current filter context. Next, we set the filters across the dates based on the two variable values set earlier. We then use these filters to calculate the sliding SUM of Sales Amount.
This is the output of the above measure
Lets focus on the values marked in red in the image. Jan 2020 and Feb 2020 values were 2387 and 3032 respectively.
So the running total for Feb should be 2387+3032= 5419, which is what we have in the output.
For March 2020, the value of Jan should drop out and sum of values for month of Feb 2020 and March 2020 should be considered. So the running total for March should be 3032 + 3137 = 6169.We have 6169 as our sliding running total for March.
Similarly for April the sliding running total should be sum of March and April i.e. 3137 + 3080 = 6217. Same for May and June with sliding running total values of 3080 + 3090 = 6170 and 3090 + 3062 = 6152 respectively.
Lets now calculate for a longer interval. Say six months.
RunningTotal =
VAR SlidingJump = 6
VAR MaxYear = MAX(DateTable[Year])
VAR MaxMonth = MAX(DateTable[Month])
VAR MonthLessThanMaxMonth =
FILTER (
ALL(DateTable),
DateTable[Month] <= MaxMonth
&& DateTable[Month] > MaxMonth - SlidingJump
&& DateTable[Year] = MaxYear
)
RETURN CALCULATE (SalesData[Sales Amount], MonthLessThanMaxMonth)
In our measure we set the value of the variable SlidingJump to 6 to correctly calculate the sliding value for last 6 months.
For the first five months the calculation works like a regular running total as the value for “sliding jump” has not yet reached the necessary month threshold.
Starting from the month six, the sales value of the earliest month i.e. January 2020 is excluded. This happens because the SlidingJump value is set to 6, and no months are skipped until the filter context reaches month 6.
If you do the math for the month of August 2020, the sliding total value is a sum of all months except January 2020.Similarly for month of September 2020 the months of January 2020 and February 2020 is skipped.
For the month of January 2021 the calculation resets and follows the similar pattern.
The above calculation displays the trailing sliding total of Sales Amount.
To calculate and display the leading sliding total of Sales Amount all we have to do is add SlidingJump to the MaxMonth variable in the calculation and change the filter condition on Month values
RunningTotal =
VAR SlidingJump = 6
VAR MaxYear = MAX(DateTable[Year])
VAR MaxMonth = MAX(DateTable[Month])
VAR MonthLessThanMaxMonth =
FILTER (
ALL(DateTable),
DateTable[Month] >= MaxMonth
&& DateTable[Month] < MaxMonth + SlidingJump
&& DateTable[Year] = MaxYear
)
RETURN CALCULATE (SalesData[Sales Amount], MonthLessThanMaxMonth)
If consider the month of January 2020 from the above image and do the math, the sliding total calculated is sum of all sales values in reverse order from the month of June 2020 till January 2020.
Conclusion
In this article, I explored the concepts of cumulative and sliding running totals in DAX highlighting how they differ and when each should be used. I also discussed the shortcomings of relying solely on built-in time intelligence functions for calculating cumulative running totals and showed how custom measures can provide more flexibility and control.
Thanks for reading !!!
