Optimizing 12m rolling average

[rhl94]

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?

[v-chuncz-msft · Accepted solution]

@rhl94 

Remove the logical test for IF in the above formula or drag fields from Date Hierarchy to Slicers.

[amitchandak]

I use this for rolling 12 months. But just need make sure we have date table

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales 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
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/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[rhl94]

My calendar table is generated using CALENDARAUTO().

When marking it as a date table, I can no longer use the date column to generate the quick measure, as it is no longer a hierarchy

[amitchandak]

Try the same without making it as date table. But use the calendar date column in formula

[rhl94]

It gives me the hierarchy back when removing mark as date table and allows me to create a quick measure using the date, however the visual is just blank. There is a relation between date from the two tables