Cumulative totals in Power BI (or Power Pivot for that matter) is a fairly common use-case. The DAX pattern for this is explained here, and you may want to read that first if you are not familiar with the pattern.
What I am going to cover in this post is an interesting nuance with cumulative totals that could trip you up if you’re not paying attention.
Setting the scene
Our data model is pretty simple for this one: We have a Date entity with all the date-related goodies we may need, and a Sales entity with a sales amount for a given date. The Date attribute from both tables/entities are used to create the relationship, and the Cumulative Sales measure is used to calculate the cumulative/running total (shown below).
Cumulative Sales =
CALCULATE
(
[Total Sales]
, FILTER
(
ALL('Date'[Date])
, 'Date'[Date] <= MAX('Date'[Date])
)
)
Creating a few visualizations in Power BI show that our Cumulative Sales measure is working as expected…so far so good.
Using a different attribute for the relationship
A common practice in the data warehousing world is to use a Date Key as unique identifier in a date dimension. This attribute is usually a number in the format yyyymmdd. I’m not going to dive into all the reasons why it is used in data warehouse environments here, but (for fun) let’s change our data model to use the Date Key attribute in the relationship between the two tables.
If we look at our visualizations again, we see a very different picture. Sales by date still looks the same, but the sales by month seems a little out of whack (image below). If you had cumulative sales at any other aggregated level (quarter, year, etc.) it would also have been incorrect.
What happened?!
Somehow the DAX measure is not working it’s magic, and reviewing it may point us to the part of the formula that uses the ‘Date'[Date] attribute as filter. If your brain works like mine, your first instinct might be to change the filter to use the ‘Date'[Date Key] attribute instead. This is what the formula and visualizations will look like after the change:
Cumulative Sales = CALCULATE ( [Total Sales] , FILTER ( ALL('Date'[Date Key]) , 'Date'[Date Key] <= MAX('Date'[Date Key]) ) )
Great…now they’re all incorrect!
The solution
The solution may not be obvious at first, but after tinkering around a bit (i.e. spending mindless head-scratching hours) you will discover that filtering the entire Date entity (instead of just one attribute) corrects the behavior.
Cumulative Sales =
CALCULATE
(
[Total Sales]
, FILTER
(
ALL('Date')
, 'Date'[Date] <= MAX('Date'[Date])
)
)
Why do we have to filter on the entire table to make it work? Well, that’s a good question and I have not gotten to the bottom of it. My initial thoughts are that it has something to do with the built-in time intelligence, but I am of course just speculating at the moment and would love to hear your thoughts on why this is happening. I’m also going to try this in Power Pivot to see if there is a difference in behavior.
Want to try this out for yourself? Here’s the source file.
Update (1/31/2018): Marco Russo provided some more information on why this is happening. As I had thought, it is due to the fact that the table is no longer marked as the Date table in Power BI. See the full explanation and a better workaround in the data model here.
Update (2/6/2018): The Feb 2018 update of Power BI Desktop now includes the ability to mark a table as a date table! This is excellent news as it will enable time intelligence without the need for the workarounds I’ve mentioned in this post.