12 Months rolling average

Hi Anonymous ,

I have done a similar thing. Have a look at the below and let me know if it solves your issue. ( it might need some adaptation).

1.Create a calculated table with the dates that will be used as a slicer
SlicerTable = VALUES(SourceTable[Date])

2.Do a measure in your IDs table.

12MonthsAverage =
VAR SelectedDate = SELECTEDVALUE(SlicerTable[Date])
VAR CurrentDate = SELECTEDVALUE(IDsTable[Date])
RETURN IF(CurrentDate <= SelectedDate &&
CurrentDate > EOMONTH(SelectedDate,-12),
Count(IDsTable[CaseId]),0)

Hope this video helps:

https://www.youtube.com/watch?v=y7ucCjtADsg&list=PLApPcvU5-R26t4dylOnPy8A2-lr4C0C--&index=13

Hi , Anonymous 

According to your description, you want to get the "12 months rolling average".

Here are the steps you can refer to :
(1)This is my test data:

To simplify the complexity of creating data, I'm using MAX instead of your COUNT here.

I create a measure to put on the visual:

Max Value(Like your Count of Value) = MAX('Table'[Value])

(2)We can add a measure like this to get it:

12 month rolling average = var _cur_date= MAX('Date'[Date])
var _last_12_month = EOMONTH(_cur_date,-12)+1
var _last_12_month_yearMonth = YEAR(_last_12_month)*100+MONTH(_last_12_month)
var _cur_year_month = MAX('Date'[Year_month])
var _t = SUMMARIZE(ALLSELECTED('Date'),'Date'[Year_month] , "value",[Max Value(Like your Count of Value)])
var _t2 = FILTER(_t ,[Year_month]>= _last_12_month_yearMonth && [Year_month]<= _cur_year_month)
return
AVERAGEX(_t2,[value])

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Hi,

Share data in a format the can be pasted in an MS Excel file.