Calculate the average of a measure that is returning a percentage variance

[Anonymous]

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: 

Var Budget vs Actual = IF([Total Units]<>0, [Total Units]-[Budget Units], "")

Var % Budget vs Actual = IF([Total Units]<>0,(([Total Units]-[Budget Units])/[Budget Units]), "")

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. 

[v-yiruan-msft]

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

[Anonymous]

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:

Forecast Logs (+5%) =

VAR UnitsLY = CALCULATE([Total Units], DATEADD(DateTable[Date],-1,YEAR))

VAR Units2yrAgo = CALCULATE([Total Units], DATEADD(DateTable[Date], -2, YEAR))

VAR Factor = 1.05

RETURN

DIVIDE([UnitsLY]+[Units2yrsAgo],2,0)*Factor

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.

[v-yiruan-msft]

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