# Dynamically Calculate A Power BI Running Total Or Cumulative Total

The Power BI running total is the perfect way to display patterns and changes on a specified data over time.

In Power BI, there is a common combination of DAX functions that allow us to create a dynamic cumulative total (sum) on any report page.

The DAX formula that we’re about to discuss is easy to use and provides dynamic results. You can reuse the same formula combination. Just substitute different core measures or core calculations into it. This will enable you to generate cumulative totals (sums) over different calculations from a wide-scale to daily results.

Weâ€™ll be using this Quarterly Insights report that I used during the Enterprise DNA Learning Summit last May 2018 as an example.

## Using The Date Table And Total Sales In Power BI

When running a cumulative total formula, we need to have a strong date table. This also goes for any time intelligence calculations.

Letâ€™s drag these filters from the Quarterly Insights report to the sample report page. This way, we can drill into any time period.

Then, letâ€™s grab the Date field into the sample report page. This will serve as our date table.

Letâ€™s also add the Total Sales column into the sample report page.

The Total Sales is considered as a simple core measure. This is because itâ€™s easy to calculate. It can also be reused in various ways like Moving Averages or Running Totals.

Now, we need to utilize the correct combination of DAX formulas to get the cumulative total (sum).

## Formula For Calculating The Cumulative Revenue

We’ll be using this formula as an example to calculate the Cumulative Revenue for the whole month of July in 2016.

As you can see here, we already have the Cumulative Revenue result that we want. Once we change the context, the cumulative sum also changes. This is what makes it dynamic.

Letâ€™s now try to analyze the given formula.

First, we’ll use the CALCULATE function to change the context of the calculation. Then, we’ll be including the Total Sales measure.

As you can see here, the Total Sales for every single day was displayed.

## Using The FILTER Function For Running Totals

Now, weâ€™re going to use the FILTER function. This will adjust the context inside the CALCULATE function.

If we want to display the proper cumulative total, we need to manipulate the current context. This is for us to calculate not just one day, but all the days within that month as well.

As we go down the list, we need to create a wider time frame that we’re currently accumulating.

Every single row within the Cumulative Revenue column is being evaluated to a different context or filter that has been placed over the Sales table.

## Using The ALLSELECTED Function For Running Totals

Furthermore, the ALLSELECTED function removes any or all the filters from the Date table that are placed within a certain context. In this case, the context is Q3 of 2016.

Then, it reapplies those filters based on this logic.

To summarize, this part removes all filters over a 3-month window. Then, it iterates through every single one of those days to identify whether that date is less than or equal to the current max date.

## Calculating The MAX Date

Letâ€™s try to create a Max Date measure, then assign this logic to it.

This is what the MAX Date logic does.

As you can see, it evaluates to exactly the same day from the Date column.

## Different Ways To Use The Power BI Running Total

Once you understand the logic for calculating running totals, it’ll be easier to use it in different ways.

For example, if we want to calculate the Cumulative Profits, we can still use the formula for the Cumulative Revenue. We just need to alter the formula a little bit.

We need to change the name of the measure to Cumulative Profits. Then, change the Total Sales to another core measure which is Total Profits.

Using this formula, we can also get the cumulative revenue of the last quarter. We’ll name this measure Cumulative Revenue LQ.

In this example, we just need to change the Total Sales to a time intelligence calculation like the Sales LQ.

## Calculating The Revenue Difference Per Quarter

After having the Cumulative Revenue LQ measure, we can now get the difference between the revenue of the current quarter to that of the last quarter. The term for this technique is Measure Branching.

To do that, we need to create a new measure and name it Revenue Diff per Quarter. Then, we can subtract the Cumulative Revenue LQ from the Cumulative Revenue measure.

After successfully integrating the formula to the previously discussed measures (Cumulative Revenue LQ and Revenue Diff per Quarter), we can now display the visualizations for easier data analysis.

## Conclusion

The key point in this tutorial is understanding the formula and then tweaking it further to branch out to other measures.

Using a DAX formula allows you to show trends and provide a concrete comparison of measures over time.

By understanding the function of each section of the formula, you can obtain instantaneous results.

This changes how presentations are done. As long as you’re able to tweak the formula according to the information you require, your desired results will be shown straight away.

This is a good review of the technique for Power BI running total. I hope that youâ€™ll be able to implement this in your own work.

For more DAX formula combination techniques, check out the Solving Analytical Scenarios module at Enterprise DNA Online. This course module covers all formulas that you can use to solve various analysis and insights in your reports.

Enjoy working through this detailed video.

Sam

## Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

## Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

## MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

## Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

## Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

## DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

## Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

## Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

## Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

## SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

## Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

## 5 Strategy Tips For Modern Data Management

Data is the lifeblood of modern business. It is the raw material that fuels everything from...