Rolling Average of a Measure

[Lu1sT0rr3s]

Hello All -

I been trying to calculate a rolling average of a measure that I prevously calculate.

This is the measure that I calculate:

MACD MONTH = 
VAR Avg_3M_EMA =
IF(
	ISFILTERED('WYLIE'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('WYLIE'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'WYLIE'[Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -3, MONTH)),
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('WYLIE'),
					'WYLIE'[Date].[Year],
					'WYLIE'[Date].[QuarterNo],
					'WYLIE'[Date].[Quarter],
					'WYLIE'[Date].[MonthNo],
					'WYLIE'[Date].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE(
				AVERAGE('WYLIE'[Device - Preload Peak Force]),
				ALL('WYLIE'[Date].[Day])
			)
		)
)
VAR Avg_6M_EMA =
IF(
	ISFILTERED('WYLIE'[Date]),
	ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
	VAR __LAST_DATE = ENDOFMONTH('WYLIE'[Date].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'WYLIE'[Date].[Date],
			STARTOFMONTH(DATEADD(__LAST_DATE, -6, MONTH)),
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('WYLIE'),
					'WYLIE'[Date].[Year],
					'WYLIE'[Date].[QuarterNo],
					'WYLIE'[Date].[Quarter],
					'WYLIE'[Date].[MonthNo],
					'WYLIE'[Date].[Month]
				),
				__DATE_PERIOD
			),
			CALCULATE(
				AVERAGE('WYLIE'[Device - Preload Peak Force]),
				ALL('WYLIE'[Date].[Day])
			)
		)
)
RETURN
        Avg_3M_EMA - Avg_6M_EMA

I would like calculate the rolling average of the result of that measure called MACD MONTH.

For your reference, here is the pbix file.

Any help will be greatly appreciated.

[v-xuding-msft]

Hi @Lu1sT0rr3s ,

After testing, for your situation, I suggest you to create a date calendar table. And then reference the blog to have a try.

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Best Regards,

Xue Ding

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

[amitchandak]

@Lu1sT0rr3s , I use this kind of measure with date calendar

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

Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))

Rolling 11 till after 6 month = CALCULATE(sum('BI CompOrders_Trend_Tbl'[order_quantity]),DATESINPERIOD('Date'[Date],startOFMONTH(dateadd(Sales[Sales Date],6,month)),11,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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/