Trailing 12 Months in Date Dimension Table

[abhiram342]

Hi All,

I have 3 FactTables and DateDimension Table. I have 3 measures in 3 fact tables such as measure1 ( Table1), measure2 ( Table2), measure 3 (Table3). Now I want to create Trailing 12 months measure in all tables . so , I have created 3 measures such as measure1 TTM, measure2 TTM,measure3 TTM.

Is there any way I can add Column in Date Dimension, so that I can reuse measure. (( basically , I don't want to create measure1 TTM, measure2 TTM, measure3 TTM) instead use measure1,measure2, measure and drag Column from Add Date Dimension.

I'm not sure what column I need to add to Date Dimension so that I can re-use existing measure

Thanks,

Abhiram

[amitchandak · Accepted solution]

@abhiram342 , You can create rolling 12 like

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

Trailing 12 like

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

Now if want to use different measures in the same kind of calculation like trailing 12, use calculation groups

calculation groups
https://www.sqlbi.com/blog/marco/2020/07/15/creating-calculation-groups-in-power-bi-desktop/