Rolling average based on a specific date range

Question

Hi there,I would like to calculate my rolling average using a specific date range.&nbsp; For example i have data from 2013 - 2019 but i want to calculate the rolling average only for 2017 - 2019. Here is where I am at thus far (tweaked the quick measure):&nbsp;Sched Prin Sum rolling average = IF(&nbsp;&nbsp;&nbsp;&nbsp;ISFILTERED('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate]),&nbsp;&nbsp;&nbsp;&nbsp;ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),&nbsp;&nbsp;&nbsp;&nbsp;VAR __LAST_DATE = ENDOFMONTH('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Date])&nbsp;&nbsp;&nbsp;&nbsp;VAR __DATE_PERIOD =&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;DATESBETWEEN(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Date],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;STARTOFMONTH(DATEADD(__LAST_DATE, -([Moving Average Length Value]), MONTH)),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;__LAST_DATE&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;RETURN&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AVERAGEX(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATETABLE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUMMARIZE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;VALUES('Starting and Closing Principal Balance Ex Orig Date'),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Year],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[QuarterNo],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Quarter],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[MonthNo],&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Month]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;__DATE_PERIOD&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALCULATE(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;SUM('Starting and Closing Principal Balance Ex Orig Date'[Scheduled Principal Payment]),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ALL('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Day])&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;))&nbsp;The field named&nbsp;Moving Average Length Value is actually a parameter that lets me define the number of months I would like to use as part of the moving average, i want to actually use a parameter to define my starting and ending dates.&nbsp; Any suggestions?

v-juanli-msft · Accepted Answer

Hi&nbsp;Anonymous&nbsp;
Please refer to this blog
Moving Averages Controlled by Slicer
&nbsp;
Best RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Rolling average based on a specific date range

2 Replies

Related content

Rolling 12M Average measure

Rolling accumulation

3 Day Rolling Average - DAX

Rolling accumulation

Conditional Rolling Average