Here in my file I have Opening Balance for current month = TB AMount Total
I know the Forecasted Payments and Forecasted Collection.
And I calculate the closing Balance using the formula
Closing Balance =
FILTER ( ALLSELECTED( ( ‘Date’ )), ‘Date’[Date] <= MAX ( ‘Date’[Date] ) )
CM Movement = [TB Amount Total]+[ToCollect]-[Forecasted payments v2]
Now if my Closing Balance for a month is negative , i need to add an equal positive amount to it called Funding(A) , to make it the Closing Balance 0( Updated Closing Balance (B) ),
If Closing Balance is positive no need to add funding.
And then, my next month opening balance should be this Updated Closing Balance(B) .
Also in the next month , the funding should be calculated based on Updated Closing Balance(B) +CM Movement for that month
and so on…
I am getting a circular dependency error while trying to implement this Funding(A), Updated Closing Balance(B),Opening Balance
Basically the issue I am facing is When calculating Funding , it depends on the previous month Closing Balance Updated, which inturn depends on Funding for Previous month. Hence giving circular depedency error
Can anyone help?
PFA link to the file:
In the below case ,for ex:
For Sep 2020, 0 funding is correct.
But for Oct 2020, funding should be 0 (8.3-1.2=7.1 which is positive so no funding), closing balance should be 7.1
For Nov 2020, Opening balance should be 7.1
7.1-1.2 =5.9 so no funding in Nov also