Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to 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.
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.
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.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |