Calculating Reverse Cumulative or Reverse Running Total In Power BI

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. You may watch the full video of this tutorial at the bottom of this blog.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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

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

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

Sam McKay, CFA
Sam is Enterprise DNA's CEO & Founder. He helps individuals and organizations develop data driven cultures and create enterprise value by delivering business intelligence training and education.

Matrix Visualization In Power BI

I will show you formatting tricks on how to put think borders on matrix visualizations in Power BI....

Power Query Each Expression: An Introduction

Power Query is a data transformation and manipulation tool that's available in Microsoft Excel and...

VertiPaq Analyzer In DAX Studio | Power BI Tutorial

This tutorial will showcase the VertiPaq Analyzer Metrics in DAX Studio and how it helps in optimizing...

Convert The Date Table Function Into A Table Query In Power BI

I'm going to show you how to turn an M code for a date table into a table query. You may watch the full...

Creating Power BI Tables By Using UNION & ROW Function

I'm going to show you how you can create Power BI tables using a formula that combines the UNION...

Time Comparisons In Power BI: This Year vs Last Year

I want to go over how you can easily do time comparisons in Power BI and specifically calculate this...

Power BI Slicers Tutorial: Counting Selections Correctly

In today's tutorial, I'm going to work through a subtle issue with Power BI slicers that can trip you...

Rule Of Thirds: The Composition Rules

When it comes to presenting the story, we need to start with the layout. This is where the rule of...

Tips For A Successful Power BI Implementation

We'll continue our series on Power BI project planning and implementation. This time around, we'll be...

PowerApps Documentation: Using MS Docs For Expert Functionalities

Let's talk about Microsoft's PowerApps documentation and what an important resource it can be for users...

Predicting When Will Your Customers Purchase Next w/Power BI

What if you could know when your customers are likely to make their next purchase using predictive...

Power BI Challenge 14 – Emergency Services Analytics

We've been very busy here at Enterprise DNA as we continue to level up our content so that all of you...