- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Help previous sum

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2018 11:28 AM - edited 12-01-2018 11:31 AM

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*

Solved! Go to Solution.

Accepted Solutions

## Re: Help previous sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2018 09:28 PM

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

All Replies

## Re: Help previous sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-01-2018 09:28 PM

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

## Re: Help previous sum

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2018 03:01 AM

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.