Create an accurate forecasted at completion measure

[jpdamd90]

Hi,

Can anyone help or come up with a good way to work out the most accurate forecast at completion measure. The forecasted at completion currently within the dashboard is just working on IF logic where, if the current actual is greater than the budget then it just uses that as the FAC, if the current is less then it uses the Budget as the FAC.

I have a field 'elementcompletedstatus' that shows Yes when the level/element is completed. Could i use the the average variance from the budget for completed elements & levels then for the same elements on uncompleted levels that average gets added to the budget and that becomes the new FAC?

Would there be a way to ignore areas where there is no budget or if there is a ridiculous % blow out becasue a small area goes over by a lot?

Dashboard is saved in the onedrive link below. thanks so much
Power BI 

[BeaBF]

@jpdamd90 By following these steps, you can implement a more accurate and robust FAC calculation in Power BI that leverages historical performance data to forecast future costs.

1. Create Calculated Columns for Variance:

Supply_Variance = IF([COMPLETE] = "YES", [ACTUAL SUPPLY ($)] - [BUDGET SUPPLY ($)], BLANK())

Install_Variance = IF([COMPLETE] = "YES", [ACTUAL INSTALL ($)] - [BUDGET INSTALL ($)], BLANK())

Supply_Percent_Variance = IF([COMPLETE] = "YES", ([ACTUAL SUPPLY ($)] - [BUDGET SUPPLY ($)]) / [BUDGET SUPPLY ($)] * 100, BLANK())

Install_Percent_Variance = IF([COMPLETE] = "YES", ([ACTUAL INSTALL ($)] - [BUDGET INSTALL ($)]) / [BUDGET INSTALL ($)] * 100, BLANK())

2. Calculate Average Variance:

Average_Supply_Variance = AVERAGEX(FILTER('Table', 'Table'[COMPLETE] = "YES" && [BUDGET SUPPLY ($)] > 0), [Supply_Percent_Variance])

Average_Install_Variance = AVERAGEX(FILTER('Table', 'Table'[COMPLETE] = "YES" && [BUDGET INSTALL ($)] > 0), [Install_Percent_Variance])

3. Apply Average Variance to Incomplete Elements:

Forecast_Supply = IF([COMPLETE] = "NO", [BUDGET SUPPLY ($)] * (1 + [Average_Supply_Variance] / 100), [ACTUAL SUPPLY ($)])

Forecast_Install = IF([COMPLETE] = "NO", [BUDGET INSTALL ($)] * (1 + [Average_Install_Variance] / 100), [ACTUAL INSTALL ($)])

4. Calculate FAC:

FAC = IF([COMPLETE] = "NO", [Forecast_Supply] + [Forecast_Install], [ACTUAL SUPPLY ($)] + [ACTUAL INSTALL ($)])

5. Filter Outliers and Zero Budgets:

Valid_Forecast_Supply = IF([BUDGET SUPPLY ($)] > 0 && ABS([Supply_Percent_Variance]) <= 200, [Forecast_Supply], BLANK())

Valid_Forecast_Install = IF([BUDGET INSTALL ($)] > 0 && ABS([Install_Percent_Variance]) <= 200, [Forecast_Install], BLANK())

Valid_FAC = IF(NOT(ISBLANK([Valid_Forecast_Supply])) && NOT(ISBLANK([Valid_Forecast_Install])), [Valid_Forecast_Supply] + [Valid_Forecast_Install], BLANK())

BBF

[jpdamd90]

Thanks BBF,

I've created all those columns and the output of the FAC shows as below. How can i change it so that if the Budget + the variance is higher than the current actual that it takes the Budget + variance as the FAC?

Also will the formulas take in to account the element that the variance is associated with? Eg, if Jumpform has 3 levels completed and the average variance of the 3 levels is 6%, and SLAB (NON PT) has 3 levels completed with an average variance of 8% then any uncompleted jumpform elements would show the FAC as budget + 6% where as any SLAB (NON PT) would show as budget + 8%?

Hope that makes sense. Thanks

[BeaBF · Accepted solution]

@jpdamd90 if the goal is to ensure that the Forecast At Completion (FAC) for uncompleted elements is calculated based on the average variance of completed elements of the same type (Element) and Level, you can do this steps:

1. Create a measure for the variance calculation for completed levels:

Variance =
IF(
'Table'[COMPLETE] = "YES",
DIVIDE('Table'[ACTUAL SUPPLY ($)] - 'Table'[BUDGET SUPPLY ($)], 'Table'[BUDGET SUPPLY ($)], 0),
BLANK()
)

2. Create a measure to calculate the average variance for each element:

Average Variance =
CALCULATE(
AVERAGE('Table'[Variance]),
ALLEXCEPT('Table', 'Table'[Element])
)

3. Create a calculated column for the FAC:

FAC =
IF(
ISBLANK('Table'[Variance]),
IF(
('Table'[BUDGET SUPPLY ($)] * (1 + 'Table'[Average Variance])) > 'Table'[ACTUAL SUPPLY ($)],
'Table'[BUDGET SUPPLY ($)] * (1 + 'Table'[Average Variance]),
'Table'[ACTUAL SUPPLY ($)]
),
'Table'[ACTUAL SUPPLY ($)]
)

In this way, the FAC for uncompleted elements will reflect the budget plus the average variance of the same element type, ensuring accurate forecasting.

if it's ok, please accept my answer as solution.

BBF