Power BI Cumulative Totaling

Martin Schoombee runs into an interesting issue with cumulative totals in Power BI:

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 Keyattribute 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.

The answer is not immediately intuitive, so it’s good to know this ahead of time rather than have to struggle with it later.

Related Posts

Custom Power BI Date and Time Formats

Chris Webb continues a series on Power BI custom formats: In my last post I showed lots of examples of how Power BI’s new custom format string feature can be used to format numbers. This post, looking at dates and times, will be a bit different for two reasons: there are a lot more useful examples of […]

Read More

Strong and Weak Power BI Relationships

Alberto Ferrari takes us through the two different kinds of relationships in Power BI: A relationship in a Tabular model can be strong or weak. In a strong relationship the engine knows that the one-side of the relationship contains unique values. If the engine cannot ensure that the one-side of the relationship contains unique values for the key, then the […]

Read More

Categories

February 2018
MTWTFSS
« Jan Mar »
 1234
567891011
12131415161718
19202122232425
262728