Trailing 12 full rolling

Hi Anonymous 

For requiremnet:

today is 26 June then I want to have to sales of May and 12 back  and 24 and 36 months back.

 

My understanding is :

previous month->sales of May

12 back->sales of 12 back (2018/6~2019/5)

24 back->sales of 24 back (2017/6~2019/5)

36 back->sales of 36 back (2016/6~2019/5)

 

If my understanding is correct,

Create measures

previous month =
CALCULATE (
    SUM ( Sheet6[sale] ),
    FILTER (
        ALLSELECTED ( Sheet6 ),
        Sheet6[date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
            && Sheet6[date]
                >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
    )
)

12 back =
VAR this_month =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
RETURN
    CALCULATE (
        SUM ( Sheet6[sale] ),
        FILTER (
            ALLSELECTED ( Sheet6 ),
            DATEDIFF ( Sheet6[date], this_month, MONTH ) >= 1
                && DATEDIFF ( Sheet6[date], this_month, MONTH ) <= 12
        )
    )
24 back =
VAR this_month =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
RETURN
    CALCULATE (
        SUM ( Sheet6[sale] ),
        FILTER (
            ALLSELECTED ( Sheet6 ),
            DATEDIFF ( Sheet6[date], this_month, MONTH ) >= 1
                && DATEDIFF ( Sheet6[date], this_month, MONTH ) <= 24
        )
    )
36 back =
VAR this_month =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
RETURN
    CALCULATE (
        SUM ( Sheet6[sale] ),
        FILTER (
            ALLSELECTED ( Sheet6 ),
            DATEDIFF ( Sheet6[date], this_month, MONTH ) >= 1
                && DATEDIFF ( Sheet6[date], this_month, MONTH ) <= 36
        )
    )

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.