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

## 5 Strategies To Enhance Your Power BI DAX Skills

In this tutorial, youâ€™ll learn the general strategies to improve your DAX or M capabilities. This is an...

## ALL Function in Power BI – How To Use It With DAX

Did you know that the ALL function can be used to modify the context of a particular calculation in...

## Calculations In Power BI Using Measure Branching

Measure Branching is a technique in making calculations in Power BI. It's not something you'll hear...

## Power BI Ranking In Hierarchical Form

Today, we will learn how to calculate Power BI ranking in a hierarchical form which is a little bit...

## Understanding Power BI Aggregations

Now, let's delve deeper into one of the most important concepts in Power BI calculations â€” the...

## Using Python In Power BI | Dataset And String Function

In this tutorial, we'll discuss how you can create and prepare Dataset and String Function using Python...

## Announcing The Enterprise DNA Submit A Showcase Program

Today we are launching a brand-new program here at Enterprise DNA. We are looking to collaborate with...

## Inventory Management Reports To Show Trends In Sales

This Enterprise DNA Power BI Showcase focuses on Inventory Management. You may watch the full video of...

## Huff Gravity Model Analysis in Power BI

In this tutorial, we'll learn how to do a Huff Gravity Model analysis in Power BI. We can use this...

## Excel Hacks Every Business Should Know

No matter what industry you belong to, having a better understanding of Microsoft Excel gives you a...

## Power BI Report Example For An Optical Dataset

A lot of you may know that we have an ongoing Power BI Challenge. One of our recent Power BI report...

## AVERAGEX: Calculating Average Per Day In Power BI

Here I'm going to show you how to use the function AVERAGEX with DAX in Power BI. You may watch the...