Rolling 12m Average with Different Data Columns

[Anonymous]

Is it possible to do a rolling 12 month average for data that is seperated out by year into different tables. For example I have tables that are saved as 2018, 2019, 2020, and 2021. How can I write a rolling 12 month average for actual amounts with differentation among these columns?  Another issue too is that my data is not seperated by exact dates only month names (Jan - December).  If my data were sorted all into one I could simply use quick measurement.  Is this something that would be easier to maniuplate my source data?

[amitchandak]

@Anonymous , my advice would be to append these table. and then use date table and formula like

Rolling 12 = CALCULATE(AverageX(Values('Date'[Month Year]) , calculate( sum(Sales[Sales Amount]) ) ) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

With multiple tables. Make sure you create a date from the month year(if you do not have one) and join all of them with the common date table .

Create sum measure2 in each table

TM1 = Sum(Table1[Value])

TM2 = Sum(Table2[Value])

TM3 = Sum(Table3[Value])

TM4 = Sum(Table5[Value])

Now create same formula like

Rolling 12 = CALCULATE(AverageX(Values('Date'[Month Year]) , [TM1]+[TM2]+[TM3]+[TM4]) ,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.