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(...
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.

v-juanli-msft's avatar
v-juanli-msft
Community 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
    Community 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.