Cumulative total query

[CloudMonkey]

Hi,

I know the following formula works to find the cumulative total but I don't fully understand how it works:

[CumulativeProducts] :=
CALCULATE (
    SUM ( Sales[Quantity] ),
    FILTER (
        ALL ( 'Date' ),
        'Date'[Datekey] <= MAX ( 'Date'[Datekey] )
    )
)

I'm reading the 'definitive guide to dax'. It says the MAX section means "the maximum value of DateKey in the current context". And that "because the expression of part of the CALCULATE filters, it still works in the original filter context".

What I don't understand is why would the original filter context apply if we are using the "ALL ('Date')" function? Why doesn't MAX('Date'[Datekey']) return the maximum in the whole date table?

Does the FILTER section return all rows of the Date table and then CALCULATE further reduces those rows to the existing filter context? If so how does CALCULATE know to ignore dates greater than current filter context's date, but not to ignore dates less than the current filter context's date?

Thanks for any help,

CM

[louisvp]

Dear Community, I want to create a simular table but then in Power Query using the m Query language. Could someone help me with this? When I use the DAX code in Power BI Desktop, my Desktop file beocomes very slowly: Running total size = CALCULATE ( COUNTA (Data [StaffLevelName]), FILTER ( ALL (Data) , Data [FiscalStartYear] <= MAX (Data [FiscalStartYear])) ) Thank you very much. Kind regards Louis van Paassen

[Cmcmahan]

So this works because you have your dates and Sales data in different tables.

The first part of the CALCULATE expression is easy.  You want the sum of all values in Sales[Quantity].

The second part is creating the filter.  Any filters applied here override current filters.  However, this is only overriding filters on the 'Date' table.  So what happening is the FILTER is starting with ALL 'Date' values, and then filtering to only return rows where the [Datekey] is <= MAXimum [Datekey].  The trick here is that MAX('Date'[Datekey]) is operating at the original context, while defining the new context. 

So this will keep any filters from other tables (like Sales category), but it will return all values where the date is earlier than the current context's date.

Dealing with headaches like this, where you're not sure why MAX(Datekey) works, is why I prefer to use SELECTEDVALUE instead of MAX.  It returns a blank if you don't have a single selection for that value, and works perfectly when your context is correct.

[CloudMonkey]

Thanks @Cmcmahan 

Why does 'Date'[Datekey] operate in the new context but MAX('Date'[Datekey]) operate in the original context? Is there something special about the MAX function?

Thanks,

CM

[Cmcmahan · Accepted solution]

I actually went through on some of my own data to create a cumulative total in this manner (since the example given is not exactly the way I would do it) and I think the example might be wrong.  Have you actually implemented this cumulative total on the sample data they provide? I would be interested if it does work.  I'm sure there's some way to do it the way they say using RELATED, but when I started from scratch, I ended up with this:

Cumulative Amt = CALCULATE(SUM('Sales'[Quantity]), FILTER(ALL('Sales'), 'Sales'[Date] <= MAX('Sales'[Date]) ))

The big difference is that I'm not filtering the Date table, I'm filtering the Sales table. However, I think this example is still an example of what you find confusing.  There's nothing special about the MAX function. It could be FIRSTNONBLANK, SELECTEDVALUE, MIN, or any other expression that defines what the "previous" values for the cumulative total are.  What is special is the FILTER function.  

The filter expression is actually iterated against every row in the table (in this case ALL('Sales')) to see if it evaluates to true.  It's checking that for each row the 'Sales'[Date] is less than or equal to the value of "MAX('Sales'[Date])".  It's my understanding that because your MAX expression doesn't have a new context explicitly defined, it evaluates once at a level out, essentially evaluating against the data before the ALL is applied.  

I've looked over documentation to see if there's anywhere that the context switch is explicitly called out, or articles that explain it, but I can't seem to find any.  I've never questioned it, because how else would you be able to filter a table based on a current value? The automatic context switching is incredibly useful and feels natural to me, so I feel like I can't explain further.  Maybe somebody like @marcorusso or @Greg_Deckler might be able to give a better explanation on how this works.