Forum Discussion
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
- amitchandakSuper User
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- rhl94Advocate III
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
- amitchandakSuper User
Try the same without making it as date table. But use the calendar date column in formula
- v-chuncz-msftCommunity Support