Forecasting based on rolling 4-year average

[dkrof]

I need to predict future years as a rolling-4-years average. I manage to calculated the value for the first year without actuals, but for the life of me, I cannot manage to build a measure that includes this result of the calculation for the following years: The results up until 2020 are actuals, 2021 is correct and after that it's gibberish and it won't even return a value after 2024.

year	rolling
2015	72183
2016	78575
2017	82659
2018	88281
2019	101222
2020	103906
2021	94017
2022	73352
2023	51282
2024	25977
2025
2026
2027

rolling:=IF(

NOT(ISBLANK([BE]));

[BE];(

CALCULATE([BE];FILTER(DATESINPERIOD('Calendar'[Date];[Start];-4;YEAR);[Start]))/4

)

)

(Where [BE] is a simple sum of the actuals form my core table and [Start] is the first day of the year, also from the core table.)

I tried to figure this out based on simliar threads but was not able to produce the desired result. Thank you very much in advance for helping me resolve this!

[mahoneypat]

Glad you got it worked out.  If it is slow, make sure you leverage Table.Buffer or List.Buffer to speed things up (if you have a table or list used many times within your query).

Regards,

Pat

[dkrof · Accepted solution]

Thank you for your response. Unfortunately, neither the native visual nor a custom visual could really help as I need the actual data for further calculations.

I tried R but seeing that my company denies me admin rights on my own machine, that road went nowhere fast. In the end, I went back to good old PowerQuery and did it in M - coding the years based on the actuals to keep it generic for the future, then creating however many additional years I needed, pivoting the entire thing and converting the year-codes back to actual years. Easy but not really "pretty". 

Anyway, thank you both @v-yiruan-msft and @mahoneypat for trying to help. I really hope Power BI will get better native tools for statistics, forecasting and such in the future. Visualising data is not always enough. This scenario was actually very basic and it's a pitty that I could not find a smoother, more elegant solution.

[mahoneypat]

Have you already tried the forecast options in the Analytics panel of the native visual (next to format options)?  How about the forecast visuals available in AppSource?

This initially looks like a recursive calculation which you can't really do in DAX.  In this case, you could do it since there is only 2 cycles but to go out further wouldn't be practical.

Regards,

Pat

[v-yiruan-msft]

Hi @dkrof ，

If I understanding correctly, the forecast is equal to the average value of previous 4 years. For example: Year 2021, forecast 2021=(2017+2018+2019+2020)/4. How about the forecast for year 2022? Will it equal to (2018+2019+2020+2021)/4? There is one problem here. The forecast for 2021 is virtual value, so it will not be used in the following calculation of forecast 2022,2023 and so on.... If my understanding is wrong, please provide the correct calculation logic for forecast. Thank you.

Best Regards

[dkrof]

Hi @v-yiruan-msft 

You understand it excatly right. Is there any way I can achieve this?

[v-yiruan-msft]

Hi @dkrof ,

I tried to find a suitable method to obtain the forecast values after 2021, but failed... I'm sorry for that...

Hi @mahoneypat , 

Could you please help check it? @dkrof want to get the forecast values for the future years. The sample data and calculation logic as follow:

Sample data:

year	rolling
2015	72183
2016	78575
2017	82659
2018	88281
2019	101222
2020	103906

Calculation logic:

2021=(2017+2018+2019+2020)/4

2022=(2018+2019+2020+2021)/4

2023=(2019+2020+2021+2022)/4

....

I'm stuck on calculating the forecast value of year after 2022... Could you please help on it? Thank you in advance. Appreciate your help.

Best Regards