Rolling 3 month average Run Rate to forecast rest of year

[nokhse]

HI 

New enough to BI and running into a few problems in doing some forecasting.

Say i have 3 months actuals per Month

Jan   10

Feb 20

 Mar 25

I want to forecast per month for the rest of the year which will be the sum of the above 3 months divided by the number of days in those months (90days) and multiplied out for each month

Apr - (10+20+25)/(31+28+31)* 30 days in April 

May (10+20+25)/(31+28+31)* 31 days in May etc etc

I can calculate the Actuals for the 3 months but cant do anything else!!!

thanks

[Anonymous · Accepted solution]

HI @nokhse,

You can try to use the following measure formula to calculate the rolling average for the three months:

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 2, 1 )
VAR endDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, 1 ) - 1
RETURN
    DIVIDE (
        CALCULATE (
            SUM ( Table[Value] ),
            FILTER ( ALLSELECTED ( Table ), [Date] >= prevDate && [Date] <= endDate )
        ),
        COUNTROWS ( CALENDAR ( prevDate, endDate ) )
    )
        * DAY ( endDate )

Regards,

Xiaoxin Sheng

[alya1]

Hi Xiaoxin,

Thank you for your previous reply, it is super helpful!

Can I ask a follow up question please? I have calculated the measure by 12 months average but now would like to display it as line graph with Year on x-axis. I noticed it only makes sense when I drill down to month, but I need it to sum the monthly value for year total.

[foodd]

Hello @alya1 , rather than add a new question to a Solved question two

years old, please remove the reply and create a fresh post with your question.

This allows members of the Community to treat the request in the

propery context.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you.

[Anonymous]

Follow up Question: how to get it for the rest of the year? or Next 18 months?