Forum Discussion

rhl94's avatar
rhl94
Advocate III
6 years ago
Solved

Optimizing 12m rolling average

Hi

I'm looking to create a measure with 12 month rolling average. I've tried the quick measure "rolling average" and it works fine - except that I can't use any filter on it, otherwise it wont show anything. - e.g. can't filter the visual for specific clients or only last 2,5 years.
The Quick measure looks like this:

Autogenerated rolling average = 
IF(
	ISFILTERED('DB'[DateCreated]);
	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('DB'[DateCreated].[Date])
	VAR __DATE_PERIOD =
		DATESBETWEEN(
			'DB'[DateCreated].[Date];
			STARTOFMONTH(DATEADD(__LAST_DATE; -12; MONTH));
			__LAST_DATE
		)
	RETURN
		AVERAGEX(
			CALCULATETABLE(
				SUMMARIZE(
					VALUES('DB');
					'DB'[DateCreated].[Year];
					'DB'[DateCreated].[QuarterNo];
					'DB'[DateCreated].[Quarter];
					'DB'[DateCreated].[MonthNo];
					'DB'[DateCreated].[Month]
				);
				__DATE_PERIOD
			);
			CALCULATE(
				[Averageprice of closed cases];
				ALL('DB'[DateCreated].[Day])
			)
		)
)


So I've tried to create my own, however it is running very slow (10-15 sec to visualize compared to the instant of the quick measure):

12m rolling average = 
CALCULATE (
[Averageprice of closed cases];
FILTER ('DB';
MIN ( 'Calendar'[Date] )
> DATE ( YEAR ( 'DB'[DateCreated] ); MONTH ( 'DB'[DateCreated] ); 1 )
&& MIN ( 'Calendar'[Date] )
<= DATE ( YEAR ( 'DB'[PGDateCreated] ); MONTH ( 'DB'[PGDateCreated] ) + 12; 1 )
)
)

 

NB: There is no relation between the Calendar table and the DB table in order for this to work.

 

Any suggestions to optimize this?

5 Replies