Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Trailing 12 full rolling

Hi, 

 

I want to calculate the sum of sales 12, 24 and 36 full month back. 

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    HI Anonymous ,

    You can write a measure with date function  to manually filter records:

     

    Measure =
VAR currDate =
    MAX ( Tabe[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALL ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
                && [Date] <= currDate
        )
    )

     

    Notice: you can modify bold part to change cumulative month range.

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous ,

    You can write a measure with date function  to manually filter records:

     

    Measure =
VAR currDate =
    MAX ( Tabe[Date] )
RETURN
    CALCULATE (
        SUM ( Table[Amount] ),
        FILTER (
            ALL ( Table ),
            [Date]
                >= DATE ( YEAR ( currDate ) - 1, MONTH ( currDate ), DAY ( currDate ) )
                && [Date] <= currDate
        )
    )

     

    Notice: you can modify bold part to change cumulative month range.

    Regards,

    Xiaoxin Sheng