Reply
Frequent Visitor
Posts: 2
Registered: ‎12-01-2018
Accepted Solution

Help previous sum

[ Edited ]

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


Accepted Solutions
Highlighted
New Contributor
Posts: 605
Registered: ‎02-29-2016

Re: Help previous sum

@Lenastray

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

View solution in original post


All Replies
Highlighted
New Contributor
Posts: 605
Registered: ‎02-29-2016

Re: Help previous sum

@Lenastray

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

Frequent Visitor
Posts: 2
Registered: ‎12-01-2018

Re: Help previous sum

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.