Forum Discussion
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 ba...
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.