Adjusted Forecast using Actuals based on a KEY

DAX DAX Calculations
1

This post is related to Forecast to Actuals formula

I have my Forecast and Actual Spend for Capital Expenses. These are reconciled at project level using a KEY (ie. C-IN-CAMEL).

For closed Q (Q1 and Q2), Spend would be the Actual for the project thus “overwriting” the Forecast. This is occurring for Q1 (Adj. Fct = Spend), but not for Q2 as shown below:

image
image1437×126 17.8 KB

Moreover, the Q1 92K delta should’ve decreased the Forecast of Q2 by that amount (it should’ve been $ 126K instead of 218K). Then again, Q2 Adj. Fct must be the Spend of 75K, hence increasing Q3 by 51K (126 - 75), so it should be $ 1,842K (1,791 + 51) and so on. That’s exaclty what I’m seeking for.

Any support here would be greatly appreciated.

Best
CAPITAL EXPENSES.pbix (755.2 KB)

2

Hi @SamSPAIN

I created second matrix. Hope this meets your requirement.

Expenses
Expenses892×268 43.7 KB

CAPITAL EXPENSES.pbix (725.1 KB)

3

Thanks a lot @Rajesh! This works perfectly well! Cheers