Calculate R12 based on year and month fields

[Hichamas4]

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.

[Anonymous · Accepted solution]

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.

[FreemanZ]

hi @Hichamas4 

what is R12?

[Hichamas4]

Rolling 12 Months!

[FreemanZ]

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.

[Hichamas4]

For the moment, I do not want to add no dim.date table. I only want to use the year.period column I created in the actual table.