Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Cumulative (Running) Totals in DAX

A common Measure that you’ll probably find useful in PowerPivot or SSAS Tabular Models is finding running totals.  For example, you may want to see total sales of a product as it accumulates over time, or for inventory models the total on hand at a given time.  You can find more tips and tricks at my blog, www.bipatterns.com.

Let’s start with a base measure in a very simple pivot table.
Total Sales :=
CALCULATE ( SUM ( FactSales[SalesAmount] ) )

Total Sales

Now lets take our first attempt at computing a running total.  This is the most intuitive formula, but it has one common pitfall that isn’t necessarily easy to see right away.
Cumulative Total Sales :=
CALCULATE (
    [Total Sales],
    FILTER (
        ALL ( DimDate[Datekey] ),
        DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
    )
)
Key parts of the Formula: The use of ALL(DimDate[DateKey]) results in the current context being ignored, so dates outside of the current pivot row context will be analyzed.  The second key step is the comparison of DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ).  This means that all dates in the DateKey column that are before the current pivot table row context will be calculated.

If we put this measure on a table, we’ll get the correct numbers but we will have one issue remaining.

Cumalative Total Sales

The formula returns a number for dates that have no sales.  We need to add some error handling, which is outlined below.
Cumulative Sales (Correct) :=
IF (
    COUNTROWS ( FactSales ) > 0,
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( DimDate[Datekey] ),
            DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
        )
    ),
    BLANK ()
)

The IF Function checks to make sure that there are sales in the current selected context, otherwise returning blank.  You can see the difference between the two measures below:

Cumalative Total Sales (Correct)

If you have any questions for me, you can reach me via LinkedIn or in the PowerBI Community.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@sdjensen

 

Give the following a try:

Cumulative Sales:=IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(FactSales[DateKey]),ALL(FactSales)),CALCULATE([Total Sales],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

View solution in original post

10 REPLIES 10
Mohammadreza_ba
New Member

hi guys

I have a big problem 

I use this code to find cumulative (running total) in the SSAS tabular but I give this result:

=CALCULATE(SUM (TEST_X[SALE]),FILTER(ALL(TEST_X[DATES1]) ,TEST_X[DATES1]<=max(TEST_X[DATES1]) ))

Capture.PNG

 

would you please help me to solve it?

Vikhil27
New Member

Can some one help me in this cumulative totals are getting Zeros I want 67 instead of 0

FYI: My Fisal Year Starts From Oct-sept

Vikhil27_0-1646743572360.png

Dax using:

Total Program Launch = CALCULATE ([Test1],
FILTER (
ALLSELECTED( 'Calendar_New'),
'Calendar_New'[sortyearmonth]<=MAX('Calendar_New'[sortyearmonth])
&& 'Calendar_New'[FY_Year] = MAX ('Calendar_New'[FY_Year])
)
)
james_m
Frequent Visitor

Best solution:
Running Total =
VAR RunningTotal= CALCULATE([Amount], FILTER(ALL(DimDate), DimDate[Full Date]<=MAX(DimDate[Full Date])))
RETURN IF(COUNTROWS(FctTable), RunningTotal)
Luddish
Frequent Visitor

I want to make a graph to display "orders delivered late/orders delivered" over time.

Our companys goal is to reach 95% within 30 minutes from the planned delivery time.

 

%30 = 1-([SumLate]/[SumDelivered]) works fine when we don't look at a specific time.

 

The problem I have is that the current percentage shown is only at that specific time and not the total up untill that time.

 

I have [SumLate] which is

"SumLate = CALCULATE(COUNT('Ecommerce Order'[Order id]);FILTER('Ecommerce Order';'Ecommerce Order'[Order status]="Delivered" && [LateDelivery]>30))"

[LateDelivery] is 'Ecommerce Order'[Actual Delivery Time] - 'Ecommerce Order'[Planned Delivery Time]

 

And I have [SumDelivered]

SumDelivered = CALCULATE(COUNT('Ecommerce Order'[Order id]);FILTER('Ecommerce Order';'Ecommerce Order'[Order status]="Delivered"))

 

I want to make this work as a Linegraph but I'm not sure how to apply the Datefilter-function since I'm only looking at times in the current day.

Anonymous
Not applicable

Super article! Thanks.  Smiley Very Happy

Anonymous
Not applicable

Hello,

 

thank you for this solution. But the running total starts from the very first day of the data base and keeps totaling to the last day.

 

Would it be possible to restart totaling every year or even month to be able to do cumulative total comparisons vs previous year.

 

Regards

 

Mark

 

Anonymous
Not applicable

Hi @Anonymous  ,

Have you tried using a visual level filter for the time period you want? Hope it will work. 

 

Ex: If you are showing data in a 'Table' or 'LineChart' visual, add a 'visual level filter' for the required time period i.e. current month, last 3 months, next 3 months, etc. 

sdjensen
Solution Sage
Solution Sage

Do you have an alternative solution instead of using IF ( COUNTROWS(FactSales) > 0? Lets say you drill down to day level and some days don't have any rows in your fact then a value will not be calculated for these days.

 

IF (
    COUNTROWS ( FactSales ) > 0,
...
...
...
    BLANK()
)
/sdjensen
Anonymous
Not applicable

@sdjensen

 

Give the following a try:

Cumulative Sales:=IF(MIN(DimDate[Datekey])<=CALCULATE(MAX(FactSales[DateKey]),ALL(FactSales)),CALCULATE([Total Sales],FILTER(All(DimDate[Datekey]),DimDate[Datekey]<=MAX((DimDate[Datekey])))),BLANK())

 

Please mark it as a solution or give a kudo if it works for you, otherwise let me know if you run into an issue and I'll do my best to assist. 

 

Thanks,

Ryan Durkin

Anonymous
Not applicable

This suggestion has gotten me closer.  Thank you!

Instead of Blank() I used 0, however, since it's a cumulative count I'm going for, I'm thinking instead of zero I would actually need the value show before.  So that 0 plus the last value.  Any ideas on getting that for the true part of this IF statement?

 

Cumulative Sales (Correct) :=
IF (
    COUNTROWS ( FactSales ) > 0,
    CALCULATE (
        [Total Sales],
        FILTER (
            ALL ( DimDate[Datekey] ),
            DimDate[Datekey] <= MAX ( ( DimDate[Datekey] ) )
        )
    ),
    BLANK ()
)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.