In this video, we’ll continue to learn about time-intelligence functions in DAX and look at the difference between the Year-to-Date and Running Total calculations.
Some people think that the running total calculation is the same as Year-to-Date. However, that is not the case. In fact, when you’re looking at the two calculations within one year period, the two are identical.
However, when you look at the values over the one year period or longer, you’ll immediately see the difference. The Year-to-Date calculation will reset after the end of each year. Meanwhile, the running total will accumulate all the values from the beginning up to the current date without any resets. Straight from the beginning to end.
Year-to-Date
To calculate the YTD, we use the DATESYTD function inside the CALCULATE:
Sales YTD = CALCULATE( SUM (Sales[Sales Amount]),
DATESYTD (Dates [Date]) )
Running total
Actually, DAX doesn’t have a separate function for runnint total, like it does for YTD calculations. That is why, we need to calculate running total using the combination of the CALCULATE and FILTER functions.
Running Total = CALCULATE( SUM (Sales[Sales Amount]),
FILTER( ALL(Sales), Sales[DateKey] <= MAX( Sales[DateKey])))
The above formula takes the entire Sales fact table (that’s why we have the ALL function). And then for each row calculates the sum of all sales up to the date, which is less than or equal to the latest date at that point.