Circular Dependency error while trying to calculate cumulative

[Antmkjr]

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 =

CALCULATE (

[CM Movement],

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 dependency error

Can anyone help?

https://drive.google.com/file/d/1d5T646t88v-28JFsnkkHgsjD5qRPxjx_/view?usp=sharing

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

[Antmkjr]

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 dependency error

[Anonymous]

It sounds like a recurrence... Sorry, you're a bit out of luck here. DAX does not allow recurrences. When you are calculating a column, this column's value cannot depend on itself in DAX. For such calculations you'd need to use Power Query. A dynamic calculation like this is not possible, I'm afraid. Maybe you should calculate this in Power Query and then use the calculated column in DAX...

[Antmkjr]

Could you please provide an example

[Antmkjr]

@AntrikshSharma 

@dedelman_clng 

@Anonymous 

@amitchandak @Greg_Deckler 

[Antmkjr]

To explain further,

Funding for Current Month(Sep 2020) can be calculated using Opening Balance+ Current Month Movement. (X) (The opening balance for Current Month, Sep 2020 is known)
Check if X is negative , then add an equivalent amount (say X is -20M, then funding is 20M).
So now the Closing Balance for Sep 2020 ,is 0

So for Oct 2020, the Opening Balance is 0. Suppose Net Movement for Oct 2020 is 35M
So 0+35M is positive
So Funding for October 2020, is 0
Closing Balance for Oct 2020 is 35M

Opening Balance for Nov 2020 is 35M
Suppose Net Movement for Nov 2020 is -70M
So 35-70M= -35M
So the Funding is 35M
Closing Balance for Nov 2020 is 0M

It should be Previous Month Closing Balance+ Movement for that month that has to be check if negative or positive, to determine if funding is required or not

Note, In the above example, for Nov 2020, the Funding is 35M , and not 70M