Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculate rolling 12

Good morning,

 

How can I calculate rolling 12 without using a calendar table, but using the date field that comes from the database which has the format 01012021 for example.

 

Thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    I think you can build a number type YearMonth column and a Rank column to calcualte rolling 12.

    YearMonth and Rank columns are calculated columns.

    YearMonth = RIGHT('Sample'[Date],4)*100+LEFT('Sample'[Date],2)
    RANK = RANKX('Sample',[YearMonth],,ASC,Dense)

    Rolling 12 measure:

    Rolling 12 = 
CALCULATE(SUM('Sample'[Value]),FILTER(ALL('Sample'),'Sample'[RANK]>MAX('Sample'[RANK])-12&&'Sample'[RANK]<=MAX('Sample'[RANK])))

    Result:

    Best Regards,
    Rico Zhou

     

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

3 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    It will be ideal to convert 01012020 to a proper date, then build a Calendar Table and then use the Date and Time Intelligence functions to get your desired result.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    I think you can build a number type YearMonth column and a Rank column to calcualte rolling 12.

    YearMonth and Rank columns are calculated columns.

    YearMonth = RIGHT('Sample'[Date],4)*100+LEFT('Sample'[Date],2)
    RANK = RANKX('Sample',[YearMonth],,ASC,Dense)

    Rolling 12 measure:

    Rolling 12 = 
CALCULATE(SUM('Sample'[Value]),FILTER(ALL('Sample'),'Sample'[RANK]>MAX('Sample'[RANK])-12&&'Sample'[RANK]<=MAX('Sample'[RANK])))

    Result:

    Best Regards,
    Rico Zhou

     

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