Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all
Can someone please assist. I have some data over the course of a financial year and I am building a forecasting model. I have created two measures, one tracking variance between actual and budget as $, and one as a %. My two variance measures are:
I want to create another separate measure that gives me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year), so I can use it outside of the table in other calculations (i.e. as the factor for my forecasting model, rather than using flat, hard coded factors of 5% or 20% growth).
Can anyone assist me with the DAX for this measure, please? Can't seem to get it to work. Below is my table of data, for context.
Hi @Anonymous ,
Do you want to forecast the actual value from May 2021 to September 2021? If so, maybe you can refer to the content in the links below to implement it.
Creating Forward Forecasts in Power BI Using DAX
Showing actuals and forecasts in the same chart with Power BI
Best Regards
I have already built the forecast formula and it works perfectly, but I am using hard coded values in it (i.e. 1.05 for 5%) and I want to use the actual average % variance between actual and budget so that it is a dynamic % that adjusts the forecast. Here is my forecasting formula:
So I just need assistance with the measure itself, to give me the actual average % for the entire FYTD (excluding current month and the months up to end of the financial year)
If you see the image above, the column Var % Budget vs Actual gives a % variance between the Actual and Budget columns, and if you average it (excluding the variance of the current and future months in the financial year), you get a % value. That is the value I want to be able to grab and use in my forecasting formula as a measure, instead of a hard coded factor of 1.05 (5%) as it shows more accurately what we are likely to finish the year on, above budget. I'm having trouble getting the syntax of this to work in a measure.
Hi @Anonymous ,
What's the calculation logic of var Factor? The below formula is correct or not?
Factor= average((Budget/Actual)/(Actual-Budget))
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |