topic Re: Remove Months from Cumulative Total in DAX Commands and Tips

Remove Months from Cumulative Total

TimmK — Tue, 13 Sep 2022 07:34:08 GMT

I use the following measure to get my cumulative total:

Measure = CALCULATE([Cash],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))

It generally works fine. However, in the visual months are displayed that have not been reached yet (in this case October, November, December 2022).

How can I adapt my measure so that these months are not included in the visual?

Also, how I can I also accomplish the same by using relative date in the visual? I can for example select in the last 100 calendar months, but not in the last 100 calendar months AND the current calender month. How can I include the current calendar month?

Re: Remove Months from Cumulative Total

amitchandak — Tue, 13 Sep 2022 07:37:22 GMT

TimmK , Assume you have table with date

Measure =

var _max = maxx(allselected(Table), Table[Date])

return

CALCULATE([Cash],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]) && Date[Date] <= _max))

Re: Remove Months from Cumulative Total

TimmK — Tue, 13 Sep 2022 09:15:55 GMT

Thanks, I tried it, but it did not work, the result is the same as before.

var _max = MAXX(ALLSELECTED(Cash),Cash[Datum])

RETURN

CALCULATE([Cash],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date]) && 'Date'[Date]<=_max))

Re: Remove Months from Cumulative Total

tamerj1 — Tue, 13 Sep 2022 09:55:42 GMT

Hi TimmK
Please try

Measur = CALCULATE ( [Cash], 'Date'[Date] <= MIN ( 'Date'[Date] ), 'Date'[Date] <= TODAY (), ALL ( 'Date' ) )

Re: Remove Months from Cumulative Total

TimmK — Tue, 13 Sep 2022 09:44:59 GMT

I just tried it, but it results in exactly the same as previously, I still see the months October, November, December 2022 in the visual.

Re: Remove Months from Cumulative Total

tamerj1 — Tue, 13 Sep 2022 10:00:59 GMT

TimmK
Then try

Measur = IF ( MIN ( 'Date'[Date] ) <= TODAY (), CALCULATE ( [Cash], 'Date'[Date] <= MAX ( 'Date'[Date] ), ALL ( 'Date' ) ) )

*Edited

Changed MAX to MIN in the IF statement

Re: Remove Months from Cumulative Total

TimmK — Tue, 13 Sep 2022 10:11:48 GMT

Thank you very much. 🙂

It works with MAX.

I adapted the solution a bit and now it works as it should:

IF(
   MAX( 'Date'[YM] ) > FORMAT( TODAY(), "YYYYMM" ),
   BLANK(),
   CALCULATE([Cash], FILTER( ALL( 'Date' ), 'Date'[Date] <= MAX( 'Date'[Date] ) )
))