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

[v-shex-msft · 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

[ChetanWabhale]

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