Hi there,
I would like to calculate my rolling average using a specific date range. 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):
Sched Prin Sum rolling average =
IF(
ISFILTERED('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate]),
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('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Date])
VAR __DATE_PERIOD =
DATESBETWEEN(
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Date],
STARTOFMONTH(DATEADD(__LAST_DATE, -([Moving Average Length Value]), MONTH)),
__LAST_DATE
)
RETURN
AVERAGEX(
CALCULATETABLE(
SUMMARIZE(
VALUES('Starting and Closing Principal Balance Ex Orig Date'),
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Year],
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[QuarterNo],
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Quarter],
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[MonthNo],
'Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Month]
),
__DATE_PERIOD
),
CALCULATE(
SUM('Starting and Closing Principal Balance Ex Orig Date'[Scheduled Principal Payment]),
ALL('Starting and Closing Principal Balance Ex Orig Date'[CurrentDate].[Day])
)
)
)
The field named 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. Any suggestions?
