cancel
Showing results for
Did you mean:
Highlighted
Regular Visitor

## Rolling average based on a specific date range

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?
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Rolling average based on a specific date range

Hi @GeraldZ

Please refer to this blog

Moving Averages Controlled by Slicer

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support Team

## Re: Rolling average based on a specific date range

Hi @GeraldZ

Please refer to this blog

Moving Averages Controlled by Slicer

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team

## Re: Rolling average based on a specific date range

Hi @GeraldZ

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,994 guests
Recent signins: