Stop DAX Cumulative Total Calculation in Power BI

Did it happen to you that you have calculated a cumulative total (such as running total, or year to date, etc) either using the quick measures or by writing the DAX expression yourself, and then realizing that the calculation happens even for the periods without any real data? For example, you want to stop that calculation at a certain point in time. The trick is simple, in this blog article, I’ll explain how it works.

Sample Report

To show you how this works, I have a sample model including two tables, the DimDate and FactInternetSales table.

The DimDate table is marked as a date table, which means I am not using the default Power BI date table.

I have a line chart with FullDateAlternateKey (which is a date field in the DimDate table), and SalesAmount from the FactInternetSales table in one value, and Sales YTD which is a measure in another value.

The calculation for Sales YTD measure is as below;

YTD Sales = CALCULATE(SUM(FactInternetSales[SalesAmount]),
DATESYTD(DimDate[FullDateAlternateKey]))

If you are interested to learn more about how year-to-date calculation works in DAX, read my article here.

The problem in the below visual is that there is no sales happening after 31st of July 2008, But still the Sales YTD shows values all the way until end of the year.

Sometimes, this might be the desired outcome. Which in those cases, you don’t need to change anything else. Sometimes your calculation of cumulative can be a running total or etc.

Also, other scenarios can be showing the calculation results until today (or tomorrow, or yesterday), but nothing more. You want the calculation to stop at a certain point in time. Below is a simple trick of how you can do it.

Find the last date with actual values on it

If your intention is stop the calculation whenever the last actual value appears, then follow this part. For example, let’s say I want the Sales YTD calculation to happen only up to the last date that I have a sales transaction (which in this case would be 31st of July 2008). and the Sales YTD after that should not show anything.

You can find the last date that the actual value happens, but calculating the maximum date value from your actual table. In my case, that would be from the FactInternetSales table. a simple calculation like below will give me that;

var _lastActualValue=MAX(FactInternetSales[OrderDateKey])

Because my FactInternetSales table would have records only if there is an actual sales transaction, then the calculation above means that the _lastActualValue will give me the latest transaction date.

Change the Calculation with an IF statement

The last part is very simple in fact. All you need to do is to use an IF statement and do the calculation only IF the current date is less than the latest transaction date. This IF statement can be written in your return clause as below;

IF(
SELECTEDVALUE(DimDate[DateKey])<=_lastActualValue,
CALCULATE(SUM(FactInternetSales[SalesAmount]),
DATESYTD(DimDate[FullDateAlternateKey]))
)

The SelectedValue function is used to get the value from the axis of the chart and if that particular date is less than or equal to _lastActualValue, it calculates the expressions. When you don’t define the ELSE part of the IF statement, it means BLANK in the case of ELSE. And when blank is returned, the visuals in Power BI (also depends on the visual) won’t show any value at all.

So this leads the visual to appears correctly now:

The entire DAX expression for the measure is now as below;

YTD Sales - stop last date =
var _lastActualValue = MAX(FactInternetSales[OrderDateKey])
return
IF(
SELECTEDVALUE(DimDate[DateKey])<=_lastActualValue,
CALCULATE(SUM(FactInternetSales[SalesAmount]),
DATESYTD(DimDate[FullDateAlternateKey]))
)

Stop Based on Today’s date

If you want the calculation to stop at today’s date, you can easily change it to below;

YTD Sales - stop today =
IF(
SELECTEDVALUE(DimDate[FullDateAlternateKey])<=TODAY(),
CALCULATE(SUM(FactInternetSales[SalesAmount]),
DATESYTD(DimDate[FullDateAlternateKey]))
)

In my sample data this code, won’t be much different than the normal Sales YTD, because the date of writing this article is 3rd of Sep 2020, and my sample data is for years 2005 to 2008. However, it would work simply in your up-to-date data.

Stop based on other dates related to today

You can easily manipulate the expression above to achieve similar things as well;

Yesterday:

Today()-1

Tomorrow;

Today()+1

One thing to notice if you are dealing with TODAY() or any functions related to that is to remember the timezone configuration of Power BI servers. I have explained in another article how that can impact your reports and what you can do about it, read that article here.

Summary

Changing a calculation to stop at a certain point in time, is not complicated. In fact it is very simple. All you need to do is first to find out that point in time (for example, the latest date that there is actual value, or today etc). And then use an IF statement to check the date in the visual with that point in time, and only shows values up until that point in time.

Download Sample Power BI File

Download the sample Power BI report here:

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.