Remove Months from Cumulative Total

[TimmK]

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?

[tamerj1]

Hi @TimmK 
Please try

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

[TimmK]

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.

[tamerj1 · Accepted solution]

@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

[TimmK]

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] ) )
))

[amitchandak]

@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))

[TimmK]

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))