Continuous (Forecast) Rolling 3 Months Average. Use 3 month average to calculate future average.

[KPat31]

Hello,

I hope you can help me with an issue I've been having in Power BI.

I have a Power Bi report that has the 'Actuals' in a Measure and the actuals end at current month (April 2023).
I want to calculate in a different measure (or column) with the future months (up to Dec 2026) based on the last 3 month average.

So for example, in the New Measure (or column) , if there is an value in the 'Actuals' measure, then output the Actual, if not, then take the average of the last 3 months and output that. So for May 2023, it should take the average of April 2023, March 2023, and Feb 2023.

The issue I'm running into is the future calculations, I need Power BI to use the calucated 3 month rolling average for future calculations. So for June 2023, it should use the actual values from March 2023, April 2023, and the new calculated 3 month average of May 2023, and so on forth.

Here is an example:

Month	Actuals (Measure)	Rolling 3 Month Avg
April 2022	2.38	2.38
May 2022	2.45	2.45
June 2022	2.53	2.53
July 2022	2.52	2.52
August 2022	2.43	2.43
September 2022	2.37	2.37
October 2022	2.29	2.29
November 2022	2.39	2.39
December 2022	2.52	2.52
January 2023	2.31	2.31
February 2023	2.27	2.27
March 2023	2.20	2.20
April 2023	2.38	2.38
May 2023		2.28
June 2023		2.29
July 2023		2.32
August 2023		2.30
September 2023		2.30
October 2023		2.30
November 2023		2.30
December 2023		2.30

So you can see that June 2023 is using the May 2023 calcuated Rolling Average in it's math, July 2023 is using May and June's calculated Rolling Average in it's math and it continues on.

I know that eventually, it will just do an avg of an avg, and so on forth, however, this is the request.

Please let me know if anyone can help with this.

Thank you in advance!

[v-shex-msft]

Hi @KPat31,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[tamerj1 · Accepted solution]

Hi @KPat31 

Please refer to my Fibonacci table approach that can solve such Recursive calculation issue in the following post. 
https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Recursive-Calculation-and-Forecast-m...

[aj1973]

Hi @tamerj1 

Please , How did you generate your Fibonacci table?