topic Re: Rolling 3 month average Run Rate to forecast rest of year in DAX Commands and Tips

Rolling 3 month average Run Rate to forecast rest of year

nokhse — Tue, 10 May 2022 10:25:01 GMT

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

Re: Rolling 3 month average Run Rate to forecast rest of year

Anonymous — Fri, 13 May 2022 01:36:43 GMT

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

Re: Rolling 3 month average Run Rate to forecast rest of year

Anonymous — Sat, 11 Feb 2023 04:47:27 GMT

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

Re: Rolling 3 month average Run Rate to forecast rest of year

alya1 — Mon, 24 Jun 2024 20:29:44 GMT

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.

Re: Rolling 3 month average Run Rate to forecast rest of year

foodd — Tue, 25 Jun 2024 00:01:04 GMT

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.