Forum Discussion

Hichamas4's avatar
Hichamas4
Frequent Visitor
3 years ago
Solved

Calculate R12 based on year and month fields

Hi all,

 

I have a scenario where I am not using a dim.date as I only have a fiscal year and period columns which I merged to have yyyy.mm as following: 202201. 

 

I have used to calculate YTD based on this column and it works fine, but the R12 months doesn't.

 

How can I best calculate R12 in case no dim.date is available?

 

Thank you.

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Hichamas4 ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create measure with below dax formula

    RK = RANKX(ALL('Table'),CALCULATE(MAX([YYYY.MM])),,ASC)
    R12 =
VAR _rk = [RK]
VAR tmp =
    FILTER ( ALL ( 'Table' ), [RK] <= _rk && [RK] >= _rk - 11 )
RETURN
    SUMX ( tmp, [Sales] )

    3. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

      • FreemanZ's avatar
        FreemanZ
        Super User
        3 years ago

        Aha, then try to add a column in dim.date table like:
        Date = DATE([year], [period], 1)

         

        then you are free to use all the Time Intelligence Functions, like: DATESBETWEEN, DATEINPERIOD for rolling calculations.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Hichamas4 ,

    Please try below steps:

    1. below is my test table

    Table:

    2. create measure with below dax formula

    RK = RANKX(ALL('Table'),CALCULATE(MAX([YYYY.MM])),,ASC)
    R12 =
VAR _rk = [RK]
VAR tmp =
    FILTER ( ALL ( 'Table' ), [RK] <= _rk && [RK] >= _rk - 11 )
RETURN
    SUMX ( tmp, [Sales] )

    3. add a table visual with fields and measure

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.