I don't understand how running total works

[daanodinot]

I've been knocking my against the wall for hours, but I just can't understand how this measure works:

Running Total = 
CALCULATE (
    [Total Sales],
    FILTER ( 
        ALL ( 'Date' ), 
        'Date'[Date] <= MAX ( 'Date'[Date] ) 
    )
)

This indeed produces a running total, but I don't know why!

ALL ignores any filters. So why isn't the result of MAX always the max of the whole table? And subsequently, why isn't the result of this measure always the total sales of the whole table? The article says:

In RT Sales 2 [which I've renamed to Running Total for simplicity], on the other hand, MAX is executed without a CALCULATE around it. So it computes the max date in the outer filter context, created by the visual through SUMMARIZECOLUMNS. As such, it computes the last visible date in the visual, and produces the correct result.

Can anyone explain step by step what Power BI does to get the running total? I still don't get it.

[latimeria]

Running Total = 
CALCULATE (
    [Total Sales],
    FILTER ( 
        ALL ( 'Date' ),    -- apply on a table, filter not changed
        'Date'[Date] <= MAX ( 'Date'[Date] )  -- max date in the current context
    )
)

filter is an iterator.

So formula will iterate thru date table ( all dates in this case) and sum up the sales

until date is <= to max date in the current context.

Context can come from calculate , filter, visual filter, ....

Formula will be executed for each line in the visual

so if, in the visual you have

01/2022 : sumup until 01/2022

02/2022 - sumup until 02/2022 and so  on

And the total will be calculated in the same way - sumup sales for all the dates. It is not calculated by summing the rows.

All rows are calculated in their context.

Filter context in DAX - SQLBI

Hope this help.

[daanodinot]

Thank you. I think it's starting to click now.

Should I see it that MAX is always the MAX of te whole table inside FILTER and then the visual filter is applied? Or is the MAX already determined by the visual filter going into FILTER?

[latimeria]

To master the DAX, you need to understand 

Row context

Filter context

Context transition (tough to understand).

variables

Understanding context transition in DAX - SQLBI

Filter context in DAX - SQLBI

Row context in DAX - SQLBI

Variables in DAX - SQLBI

https://www.youtube.com/watch?v=P9Q3I9QGjGY

https://www.youtube.com/watch?v=pTI2ASgecGA

https://www.youtube.com/watch?v=sOMhqaaWM9Y

[daanodinot]

I'm trying - and I'm familiar with most of those resources. If you want to be more helpful, please try to answer my specific question. I think a lot of learning comes from understanding these small examples.

[daanodinot]

Thanks for your reply. It still sounds a little black box-y to me. Take this line:

'Date'[Date] <= __Max

Am I right to understand that __Max is determined by the outer filter context in the UI? And then 'Date'[Date] is what FILTER iterates?

So in essence, FILTER produces a longer and longer table: row 1, row 1–2, row 1–3, row 1–4 etc.

[Greg_Deckler]

@daanodinot It is black boxy, it's CALCULATE. It's one of the reasons I don't use it. There are far simpler running totals out there. LIke this one for instance (below). I separated out the MAX into it's own VAR to make clear that it is getting the MAX in the current context of the visual where it is being executed. However, the method in the video demonstrates a better, more clear, method IMHO. As in:

Better RT 2 = 
    VAR __Date = MAX('Dates'[Date])
    VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
    SUMX(__Table,[Value])

In other words, get the maximum date in context, so let's say you have a column chart with year and month as the axis. March in 2022 would give you March 31st, 2022. Then you just filter everything in your table that is less than that date. (The ALLSELECTED preserves things like maybe a Year slicer). Then you simply perform a SUMX across that table for the value you want. And yes, April is going to return more rows than March and May more rows than April, etc.

[Greg_Deckler]

@daanodinot Well, whether it works or not is dependent on which column you use from your data model. Use the wrong column and you get the wrong answer. You could write this perhaps more clearly as below. So, get the max date in the current context. So if you have a month as your axis then in March this would return 3/12/2022 for example. Then you are using the ALL to break the current context and put all rows in "view". Then you simply filter for dates < your maximum date and perform your calculation in that context. If you want a much more ledgeable way of doing this that in my opinion is better and doesn't use all the esoteric CALCULATE craziness, watch this video. MSHGQM - Don't Use CALCULATE! - YouTube. Simple, understandable, makes sense versus black box magic.

Running Total = 
VAR __Max = MAX('Date'[Date])
RETURN
CALCULATE (
    [Total Sales],
    FILTER ( 
        ALL ( 'Date' ), 
        'Date'[Date] <= __Max ) 
    )
)