Evaluation Context LOGIC on Running Total

[migueljoa]

The standard DAX expression for Running total is:

Cumulative Quantity :=

CALCULATE (

    SUM ( Transactions[Quantity] ),

    FILTER (

        ALL ( 'Date'[Date] ),

        'Date'[Date] <= MAX ( 'Date'[Date] )

    )

)

And usually you have a pivot with ITEMID and DATE on the horizontal/vertical axis. These two provide FILTER CONTEXT.

This might be a dumb question but how is 'Date'[Date] <= MAX ( 'Date'[Date] ) working?

I have read some books about the DAX engine, and I know that FILTER is an ITERATOR which generates a ROW CONTEXT, but then ALL overrides the FILTER CONTEXT on Dates coming from the pivot, thus my understanding is that MAX ( 'Date'[Date] ), being in a ROW CONTEXT, should be returning the maximun date from the whole Date Table, meaning that the FILTER clause always return the full date table.

My logic is flawed since for this formula to work, MAX ( 'Date'[Date] ) must consider the FILTER CONTEXT coming from the pivot on Dates (that's what I guess), but I don't understand how or why?

What am I missing? 

[Zubair_Muhammad]

Hi @migueljoa

MAX/Sum/Avg....any aggregation formulas always refer to FILTER Context.....

The only filter context that is available is from the Pivot table in your Case. So it takes the row value.....(You can use SELECTEDVALUE as well)

However if you wrap the aggregation inside Calculate function then the ROW context created by FILTER function will be transformed into FILTER context....and the filter context from the pivot would be ignored.

[migueljoa]

Hi @Zubair_Muhammad

So the Filter Context of the Pivot table is used on the MAX function because all aggregations are evaluated on a Filter Context? And ALL(Dates) overrides the filter context but only for the table that is FILTERED?

Thanks! I appreciate very much the time you have taken to answer my doubts

[Zubair_Muhammad · Accepted solution]

@migueljoa

You are perfectly correct....

Following is taken from Rob Collie and Avi Singh's famous book on Power BI

[migueljoa]

I see... I must have missed that part of the reading. Everything is crystal clear now!

Thanks for putting it out for me. Greatly appreciate it!