Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

YTD Cumulative

Hello,

I would like to make a YTD Cumulative by year without taking into account the year that is in my filter. For example if I filter on April 2022, to have the YTD accumulation that appears on 2020/2021/2022.

Would you know?

Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, Anonymous 

     

    You can create a new date table without establishing a relationship between the two tables. Please refer to the attachment for sample data.

    Measure = 
VAR _maxyear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [Year] = MAX ( 'Table'[Year] )
                && [Month] <= SELECTEDVALUE ( 'Date'[Month] ) ) )
VAR _otheryear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), [Year] = SELECTEDVALUE ( 'Table'[Year] ) ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Year] )
            = CALCULATE ( MAX ( 'Table'[Year] ), ALL ( 'Table' ) ),
        _maxyear,
        _otheryear
    )

    Result:

    The choice of year does not change the values for 2020 and 2021.

    Only the choice of month will change the cumulative value for 2022.

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

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

    Anonymous , I think you need cumulative 

     

    with help from date table

     

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

     

     

    or refer window function

     

    Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thanks for your help

     

    But what I need is a cumulative based on month no matter year filter.

     

    For example, I click on April 2020, I need to get the countrows for April 2020, 2021,2022.

    • v-zhangti's avatar
      v-zhangti
      Icon for Community Support rankCommunity Support
      3 years ago

      Hi, Anonymous 

       

      You can create a new date table without establishing a relationship between the two tables. Please refer to the attachment for sample data.

      Measure = 
VAR _maxyear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ),
            [Year] = MAX ( 'Table'[Year] )
                && [Month] <= SELECTEDVALUE ( 'Date'[Month] ) ) )
VAR _otheryear = CALCULATE ( SUM ( 'Table'[Value] ),
        FILTER ( ALL ( 'Table' ), [Year] = SELECTEDVALUE ( 'Table'[Year] ) ) )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Year] )
            = CALCULATE ( MAX ( 'Table'[Year] ), ALL ( 'Table' ) ),
        _maxyear,
        _otheryear
    )

      Result:

      The choice of year does not change the values for 2020 and 2021.

      Only the choice of month will change the cumulative value for 2022.

       

      Best Regards,

      Community Support Team _Charlotte

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.