Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
dkrof
Frequent Visitor

Forecasting based on rolling 4-year average

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!

1 ACCEPTED SOLUTION
dkrof
Frequent Visitor

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 @Anonymous 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.

View solution in original post

6 REPLIES 6
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


dkrof
Frequent Visitor

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 @Anonymous 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
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

Hi @Anonymous 

 

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

Anonymous
Not applicable

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.