Cumulative total nuances in Power BI

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

Cumulative Totals - Data Model

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.

Cumulative Totals - Sales by Date 1Cumulative Totals - Sales by Month 1

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.

Cumulative Totals - Data Model 2

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.

Cumulative Totals - Sales by Month 2

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])
 )
)

Cumulative Totals - Sales by Date 3Cumulative Totals - Sales by Month 3

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])
 )
)

Cumulative Totals - Sales by Month 1

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 Power BI Desktop (.pbix) 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.

 

Advertisements

One Reply to “Cumulative total nuances in Power BI”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s