Calculate future values based on already calculated value

[Gav10]

Hi 

Hoping soomeone can help because this is beating me!

I'm trying to create a calculated column which gives future fill figures (see below) based on actual inflow and outflow values and the latest fill value. 

The problem i'm having trouble with is trying to reference the predicted value in the 'Future Fill' column when the 'Actual Fill' is blank

I currently have;

Future Fill = CALCULATE(sum(Mytable[Actual Fill]), PARALLELPERIOD(Mytable[Date], -1, month)) - Mytable[Future outflow] + Mytable[Future inflow]

This of course is only fine when there is an 'Actual Fill' value, when the value is blank it returns 1, -2 and 4 for the future months as opposed to the desired result above.

When 'Actual Fill' is blank i need the formula to use the previous 'Future Fill' value

Does anyone have any ideas?

[Greg_Deckler · Accepted solution]

@Gav10 Smells an awful lot like recursion aka previous value which is a no-no in DAX. Previous Value (“Recursion”) in DAX - Microsoft Fabric Community

[Gav10]

@Greg_Deckler  i had a feeling it wouldn't be pretty! I've been trying all sorts so gives me some comfort that it wasn't me going mad!

[Greg_Deckler]

@Gav10 Nope, not going mad. I have tried for years and years to beat this problem and to no avail. It's like the one unsolvable problem left for me in DAX.