Rolling Totals vs Cumulative Totals in Power BI - The Different Logic Required For Either

[EnterpriseDNA]

Calculating for cumulative and rolling totals is quite common in Power BI to analyze data trends in a business. In fact, it’s one of the most highly used patterns in analyzing historical data. While the two calculations are almost the same, there’s a slight difference between them, which is related to the logic used on each.  

Both calculations will provide you with an accumulated result (e.g. sales, profits, products sold, stocks, budgets, etc.) at a given period. However, the cumulative total pattern gives you the total of something from the first date. On the other hand, the rolling total calculation could look back at a certain time window, such as 30 days, and calculate the total continuously. 

The key to take note of here is the filtering function used within the logic, such as ALL and ALLSELECTED. In this post, I’ll provide you with my Power BI tutorials on this topic to show you the logic that I created and used within the calculations. 

The first tutorial will show you the basic cumulative total pattern, which includes the DAX functions, CALCULATE, FILTER, and MAX. Here, I calculate the cumulative total revenue for a certain year and quarter, which requires the ALLSELECTED function. 

Here’s the link to the tutorial: 

Cumulative Totals Deep Dive - Power BI & DAX Formula Review 

In this tutorial, I also show how you can take the calculation even further and analyze more trend comparisons. The report is dynamic, so you can click through the timeframe (select the year and quarter) and it will automatically show you the cumulative results on the visuals. 

The second tutorial shows how you can implement a dynamic rolling total calculation to analyze data 30 days from the current date (or today). I demonstrate how you can easily change the timeframe into 90 days, for example, or any number of days that you want to analyze. I also show how you can achieve the exact results by using another function called DATESINPERIOD inside the calculate statement.  

Click on the title below to see how I did this using the ALL function. 

Calculating Running Totals In Power BI Using DAX 

Like I mentioned, the difference between the calculations lies in the logic, specifically with the use of the functions, ALL and ALLSELECTED. So, for you to understand this concept better, I’ll share with you my tutorial about these two functions in relation to cumulative and rolling total calculations. Please click on the link below. 

Another example of the rolling total calculation is by using the DATESBETWEEN function, which I demonstrate in this tutorial:

Calculate Amounts Sold Between Two Dates In Power BI w/DAX.

 

DATESBETWEEN creates a context between any two dates that you specify. In this example, I need the calculation to be dynamic, so that as I move through time, I have a number that re-calculates the results based on the time window that I open. 

Key Takeaway 

Using cumulative totals and rolling totals in Power BI is a great way to showcase trends over time. Once you understand the concept and the calculations, you can take your analysis even further by implementing measure branching, which will give you more significant insights.  

You can calculate any results that you could think of or you want to analyze cumulatively (averages, run rate, etc.) in certain time frames. You can also calculate cumulative totals up to a specific date. 

The calculations or patterns are simple, but they require a good understanding of the DAX functions to get them right. I have a lot of content in the website around this topic, and you can download the resources we provide for free. You can also copy the logic or patterns that I showcase in my tutorials to make your life in Power BI easier. 

For more tutorials and information on this topic, click the related links below. 

Cheers! 

Sam 

***** Related Links ***** 

Compare Cumulative Information Over Different Months In Power BI 

Calculating Reverse Cumulative or Reverse Running Total In Power BI 

Showing Cumulative Results vs Targets Only To Last Sales Date in Power BI 

***** Related Course Modules***** 

Solving Analytical Scenarios w/Power BI & DAX 

Time Intelligence Calculations 

Mastering DAX Calculations 

***** Related Support Forum Posts ***** 

Cumulative Total VS Cumulative Total LY – Cleaning Visual 

Cumulative Total Question 

Cumulative Total This Year vs Last 

For more cumulative total support queries to review see here…..