Forum Discussion

TimmK's avatar
TimmK
Icon for Helper IV rankHelper IV
4 years ago
Solved

Remove Months from Cumulative Total

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's avatar
    tamerj1
    4 years ago

    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

     

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      TimmK
      Icon for Helper IV rankHelper IV
      4 years ago

      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))
  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi TimmK 
    Please try

     

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

     

    • TimmK's avatar
      TimmK
      Icon for Helper IV rankHelper IV
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

        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