Forum Discussion

ryan0521's avatar
ryan0521
Frequent Visitor
7 years ago
Solved

YTD in Rolling13 months

Hello geniuses,

 

Can someone help me please to figure out how to make this formula become YTD rolling for 13 months;

 

"13 month YTD = CALCULATE('Income Periodical'[ValueSelected],DATESINPERIOD(Periods[Date],MAX(Periods[Date]),-13,MONTH))"

 

The formula above is rolling 13 months with their monthly value, and I want to have a new formula to make a cumulative amount or YTD.

 

Thank you in advance.

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi ryan0521 

    How about this measure

    Measure =
    SUMX
    FILTER (
        ALLSELECTED ( 'date table' ),
        'date table'[Date] <= MAX ( 'date table'[Date] )
            && DATEDIFF ( 'date table'[Date], MAX ( 'date table'[Date] ), MONTH ) < 13
    )
[Measure you want to sum]
)

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi ryan0521 

    Create a measure

    Measure =
CALCULATE (
    SUM ( 'main table'[value] ),
    FILTER (
        ALLSELECTED ( 'date table' ),
        'date table'[Date] <= MAX ( 'date table'[Date] )
            && DATEDIFF ( 'date table'[Date], MAX ( 'date table'[Date] ), MONTH ) < 13
    )
)

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • ryan0521's avatar
      ryan0521
      Frequent Visitor
      7 years ago

      It's not working brother v-juanli-msft, The value that I am summing is a result of a Measure, 

       

      "CALCULATE('Income Periodical'[ValueSelected],DATESINPERIOD(Periods[Date],MAX(Periods[Date]),-13,MONTH))"

       

      The result from the formula above is the value I want to sum up.

       

       

       

      • v-juanli-msft's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi ryan0521 

        How about this measure

        Measure =
    SUMX
    FILTER (
        ALLSELECTED ( 'date table' ),
        'date table'[Date] <= MAX ( 'date table'[Date] )
            && DATEDIFF ( 'date table'[Date], MAX ( 'date table'[Date] ), MONTH ) < 13
    )
[Measure you want to sum]
)

         

        Best Regards
        Maggie

         

        Community Support Team _ Maggie Li
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.