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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
KPat31
New Member

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

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!

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

3 REPLIES 3
v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
tamerj1
Super User
Super User

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
Community Champion
Community Champion

Hi @tamerj1 

Please , How did you generate your Fibonacci table?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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