Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Please explain cumulative sum work principle

So, I have the followint formula, that calculates cumulative sum for a measure

CumulativeAmount =
CALCULATE (
    SUM ( sheet1[amount] ),
    FILTER ( ALL ( calendar[Date] ), calendar[Date] <= MAX ( calendar[Date] ) )
)

This works perfect for me, but I can't understand fully how it works.

Let's say I have transactions for year 2017. The formula calculates sum of amount, but filters only the cases where Date was below the max selected date, shifting the context, so that it will include ALL the dates, not only the ones included in current context. But why then MAX(Calendar[Date]) is not 2017-12-31 ? If you override current date context, and calculate MAX, it should be the highest value of a column, which is the last day of the year (if the date table was automatically generated), no ?

Why for the left part of condition (calendar[Date]) in filter expression shifts current context (and returns all dates from the very beginning), but the right part (Max(calendar[Date])) does not, and returns the maximum date inside current context ?

I have seen this method of cumulative sum calculation in many sources, but none of them explains this particular part.

Thanks !

Show and Tell
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi Anonymous,

     

    There are two important points about this expression:

     

    The use of ALL(Date) in order to ignore the current context. In fact, FILTER iterates over the entire table, analyzing dates that are outside of the current filter context. In this way, it will return date that are lower than or equal to the current filter.

     

    The comparisons of Date[DateKey] against MAX(Date[Datekey]). When you are not familiar with DAX, these expressions look strange. However, if you recall the exact meaning of MAX , you see that it means "the maximum value of DateKey in the current context." Because the expression is part of CALCULATE filters, it still works in the original filter context. On the other hand, the expression Date[DateKey] is a column name, meaning "The value of DateKey in the current row context which is created by the FILTER during its iteration."

     

    Hope it could help you better understand how it works. :smileyhappy:

     

    Regards