Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Hichamas4
Frequent Visitor

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.

 

1 ACCEPTED SOLUTION
v-binbinyu-msft
Community Support
Community Support

Hi @Hichamas4 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1679464768856.png

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

vbinbinyumsft_1-1679464876310.png

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.

View solution in original post

5 REPLIES 5
v-binbinyu-msft
Community Support
Community Support

Hi @Hichamas4 ,

Please try below steps:

1. below is my test table

Table:

vbinbinyumsft_0-1679464768856.png

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

vbinbinyumsft_1-1679464876310.png

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
Super User
Super User

hi @Hichamas4 

what is R12?

Rolling 12 Months!

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.