Sam … please can you point me towards the technique that I need to use to get the right answer here?
I’m trying to predict a future ending balance; I have the delta values that will change it, but my issue is the beginning balance. Each beginning balance is dependent on the prior month’s ending balance. I’ve tried to lay it out in excel to explain my issue.
The green columns are the values that should result; I’ve managed to correctly generate the first 2 months of forecast (as shown by Forecast Ending Balance v3, DAX formula below) by combining v1 and v2 of the Forecast Ending Balance . I can’t then figure out how to tell the Ending Balance measure to iterate to generate the opening balance for subsequent months, without generating a circular reference.
Forecast Ending Balance $ v3 = VAR PriorEndingBalance = CALCULATE([USD Beginning Balance] + [Forecast Sales $] - [Forecast Cash Receipt Total $], PREVIOUSMONTH(DateMasterParker[DATE_KEY])) VAR EndingBalance = [USD Beginning Balance] + [Forecast Sales $] - [Forecast Cash Receipt Total $] VAR ThisMonth = TODAY() <= MAX(DateMasterParker[DATE_KEY]) && TODAY() >= MIN(DateMasterParker[DATE_KEY]) RETURN SWITCH( TRUE(), ThisMonth, EndingBalance, ISBLANK([USD Beginning Balance]), PriorEndingBalance + [Forecast Sales $] - [Forecast Cash Receipt Total $], [USD Closing Balance] )