Calculating Reverse Cumulative or Reverse Running Total In Power BI

No comments

Calculating a cumulative total, also known as a running total, in Power BI, is definitely common to most businesses due to its indispensability in performing calculations to show the total of a measure up to a certain date.

But did you know that creating a reverse cumulative total is as essential as calculating the cumulative total?

In this article we will go over on how to calculate a reverse cumulative total in Power BI using DAX.

Reviewing The Problem

This has been raised in the Enterprise DNA Support Forum as a question.

Reverse Cumulative Sum Support Forum

You can view this forum post here Reverse Cumulative Sum

A member had a distinctive requirement from December to January in which they needed a cumulative total. They wanted to make it dynamic as well, where the reverse cumulative total would be adjusted for that particular logic in the report whenever they wanted to change the year filter on the page.

Gladly, I was able to come up with an answer that was quite easy after working through a solution.

It was a simple variation on the most common cumulative total formula combination that you can use and reuse within Power BI quite efficiently.

But before we tackle the Reverse Cumulative Total, let’s first focus on the Cumulative Total.

the Cumulative or Running Total formula in Power BI

The Cumulative total, or running total, is used to display the total sum of data as it grows with time or any other series or progression. This is very useful in detecting changes in a certain logical pattern, and in determining whether the forecasting system is no longer adequate.

This image shows the formula for calculating the Cumulative Total in Power BI.

Cumulative Total pattern in Power BI

Let’s try to gradually analyze the given formula.

1. include the total sales from each date

The first thing that we need to do is to include the Total Sales from each date. 

Calculating Total Sales as the initial step in the Cumulative Total pattern in Power BI

The Total Sales is the sum of all the numbers in a column. In this particular example, the Total Sales is the sum of the Total Revenue column.

Calculating Total Sales as the initial step in the Cumulative Total pattern in Power BI

Then, using the ALLSELECTED function, we listed all the dates from the specific year (in this case 2017) inside the FILTER function . 

ALLSELECTED function as part of the Cumulative total in Power BI DAX pattern

You can also opt not to use the ALLSELECTED function if you want to display all the dates without selecting a specific year.

2. Compare the current date to the mAX DATE

We then need to compare the current date to the MAX date.

Comparison of dates to the MAX date as part of the Cumulative total in Power BI DAX pattern

This MAX function returns the maximum value in a column, including any logical values and numbers.

In this example, the MAX function determines the maximum value within the Date column. Then, all the dates that are less than or equal to the MAX date will be displayed. 

MAX function in Power BI DAX pattern

3. Evaluate the total sales using the ISBLANK() function

We also added an IF logic which evaluates the Total Sales using the ISBLANK() function. This DAX function returns TRUE or FALSE after checking whether a value is blank/zero or not

In this particular example, if the Total Sales is zero, a blank value should be returned, otherwise the value of the CumulativeTotal variable will be returned. 

ISBLANK function as part of the IF logic in Power BI DAX pattern

We used this logic for this example to display only the dates with the actual sales that we’re making, without including the dates that don’t have any data yet.

Results of calculating Cumulative total in Power BI DAX pattern

In the example below, the result under the Cumulative Sales column from February 1, 2017 was calculated by adding the current amount of Total Sales ($10,485) to the amount of Total Sales ($14,506) from January 1, 2017.

That’s also how the result from every single row under the Cumulative Sales column was calculated.

Sample results of calculating Cumulative Total or Running Total in Power BI DAX pattern

Calculating the Reverse Cumulative or Reverse running Total in power bI

Now that we have calculated our Cumulative Sales, let’s try forecasting the reverse cumulative total. To calculate it, we can still use our Cumulative Total Formula with some minor changes.

What you need to do is to apply a small adjustment to the row context filtering which occurs within the FILTER function of the formula pattern. 

FILTER function as part of calculating Reverse Cumulative Total in Power BI DAX pattern

Within the FILTER function, the current date should be compared to the MIN date instead of the MAX date. 

MIN function in Power BI DAX pattern

This MIN function returns the minimum value in a column.

In this example, the MIN function determines the minimum value within the Date column. Then, all the dates that are greater than or equal to the MIN date will be displayed. 

For instance, we calculated the particular result under the Reverse Cumulative Sales column from January 2017 by adding every single value of Total Sales from the dates below the current date (February 2017 to December 2017).

That goes for every single row under the Reverse Cumulative column

Sample results of calculating Reverse Cumulative Total in Power BI DAX pattern

The solution is simply just reversing or tweaking the formula for calculating the cumulative total that we currently have.

Conclusion

Reverse Cumulative Total definitely has the potential to prove its value in process control and forecasting, detecting when the pattern of customer demand changes, and when a forecasting system is no longer adequate. 

Most users won’t generally be using it, but this could also be an essential modification of how the CALCULATE function works in combination with the FILTER function.

When you utilize these two DAX formulas together, you’ll be able to generate quite interesting and unique forecasts for your data.

Here are some recommended links for you so you can explore cumulative total techniques inside of Power BI more.

Create Dynamic Cumulative Totals Using DAX In Power BI

Cumulative Totals In Power BI Without Any Dates – Advanced DAX

Hopefully, you can get a good understanding of how this particular calculation operates inside Power BI.

Sam

Download

Insert your email address and press Download for access to the files used in this article.

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI
FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

***** Related Links*****
Simple Filters w/CALCULATE – (1.13) Ultimate Beginners Guide to DAX
How To Calculate Budget Or Forecast Results Cumulatively In Power BI
Compare Multiple Metrics Cumulatively in Power BI using DAX

***** Related Course Modules*****
Mastering DAX Calculations
Solving Analytical Scenarios w/Power & DAX
Unique Analytical Scenarios

***** 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…..

Enterprise DNA Events

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.