Forum Discussion
Help previous sum
Hello. For calculating the cumulative total I make use of the following dax pattern:
CALCULATE (
SUM ( Table[numericValue] ),
ALLEXCEPT ( Table, Table[Dates], Table[Attribute1], Table[Attribute2] ),
Table[Dates] < EARLIER ( Table[Dates] )
)Calculate makes a context transition between the row context to an equivalent filter context, where in combination with allexcept, behave similar to partition over (Attribute1, Atributte2 order by Date) from SQL. Now, instead of retrieving the cumulative total, I need the get the sum only for the previous date for each date. It would be something like the maximum date that is smaller than the current date, but I´m having some difficulties mixing the max(Table[date]) and the earlier(Table[date]) and not loosing the context transition that makes the partition possible.
Thanks
Something like this should work. All I've done is change the first argument of the CALCULATE function to a further CALCULATE, with Table[Dates] filtered to the latest date subject to the filters of the outer CALCULATE. Also removed Dates from ALLEXCEPT.
=
CALCULATE ( CALCULATE ( SUM ( Table[numericValue] ), LASTDATE ( Table[Dates] ) ), ALLEXCEPT ( Table, Table[Attribute1], Table[Attribute2] ), Table[Dates] < EARLIER ( Table[Dates] ) )Regards,
Owen
2 Replies
Something like this should work. All I've done is change the first argument of the CALCULATE function to a further CALCULATE, with Table[Dates] filtered to the latest date subject to the filters of the outer CALCULATE. Also removed Dates from ALLEXCEPT.
=
CALCULATE ( CALCULATE ( SUM ( Table[numericValue] ), LASTDATE ( Table[Dates] ) ), ALLEXCEPT ( Table, Table[Attribute1], Table[Attribute2] ), Table[Dates] < EARLIER ( Table[Dates] ) )Regards,
Owen
It works like a charm. The innermost calculate is the last one in excecute, so it filter the lastdate there. Excelent, thank you so much Owen.
