Cashflow forecast - iterating results without circular ref

DAX DAX Calculations
1

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.

image
image.png995×250 8.02 KB 

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] )
2

Hello @peter.norbury,

For your case you probably will need a cummulative of the deltas, you can check cummulatives in this video:

After that, you will need to bring the last Ending Balance without deltas (I see you made something like this with that switch statement) and switch there to a measure where you get the End Balance month and sum the cummulatives of the deltas.

Doing this and using the CLOSINGBALANCEMONTH function should do the trick, since you are doing the deltas this function should get the result after applying the deltas to the last Ending Balance without deltas.

Tell me if this helps you out.

Best regards,

Jorge

3

Hi Jorge,

Thanks for taking a look at this. The cumulative idea is clever and I would see how it would work, but I’m afraid I don’t understand how the last Ending Balance and CLOSINGBALANCEMONTH pieces would work.

I still can’t figure out how to make the last Ending Balance without deltas replicate for each month of the date context; I can only get it to show for the initial row which then causes an ever decreasing final value as the cumulative totals increase.

In some ways my issue remains how to loop the prior month ending balance calculated value back into the measure without creating a circular formula reference.

Hope that makes some sense?

Peter

4

Hi Peter,

I see, can you get me the pbix file you are using with dummy information? That way I can get a clear look at what you are trying to do, and give you a better solution for your problem.

Best regards,

Jorge Galindo

5

Hi Jorge,

Here is a pbix file that contains the same outline that I’m trying to solve.
Forecast end balance examples v0.pbix (150.0 KB)

If I could find a way of making each row of the date context start with the ‘starting balance’ shown in v4 then your cumulative method would work.

Thanks again for taking the time to look at this.

Peter

6

Hi @peter.norbury,

Attached is the file with the iterating function that brings the last opening values and starts circling with the cummulatives of the deltas. I am not getting the exact same results as the picture at the beginning, probably I am not adding or substracting a value.

Forecast end balance examples v0.pbix (156.5 KB)

Let me know if this helps you.

Best regards,

Jorge Galindo