Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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...
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
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...
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
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
18 | |
18 | |
17 | |
17 |
User | Count |
---|---|
30 | |
25 | |
18 | |
15 | |
13 |