Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |