cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

1 ACCEPTED SOLUTION
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...

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

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
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...

Community Champion

Hi @tamerj1

Regards
Amine Jerbi

and you can follow me on

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors