cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GeraldZ Regular Visitor
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
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
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
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

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 6 members 4,994 guests
Please welcome our newest community members: