Circular Dependency error while trying to calculate funding based on Closing Balance+Movement

DAX DAX Calculations
1

AnuTomy_0-1600946797444.png

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

Can anyone help?

PFA link to the file:

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

In the below case ,for ex:

AnuTomy_0-1600949185945.png

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

3

Hi,

if i get you problem correctly then you only need to check if fund at the time of closing is negative or not then return

Funding = 
 Var fund =IF (
    HASONEVALUE ( 'Date'[Year] ),
    IF (
        [CM Movement] < 0,
        [CM Movement] * -1
    ),      
        CALCULATE (
                    SUMX (
                              FILTER ( VALUES ( 'Date'[Year] ), [CM Movement] < 0 ),
                               [CM Movement] 
                         )
                 )
)
 
return
if (fund<0,fund)
4

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

With the below formula,
Funding =
Var fund =IF (
HASONEVALUE ( ‘Date’[Year] ),
IF (
[CM Movement] < 0,
[CM Movement] * -1
),
CALCULATE (
SUMX (
FILTER ( VALUES ( ‘Date’[Year] ), [CM Movement] < 0 ),
[CM Movement]
)
)
)

return
if (fund<0,fund)

It is not considering Previous Month Opening Balance to determine Funding.
It is Just considering Selected Month Movement.
That should not be the case . 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

5

Hi ,
Is this what you want
image

if some thing i missed plz correct me on that so i try to solve it.

Just need to change the opening balance measure and the Funding measure with above change which i shared with you earlier

Opening Balance = 
   VAR earlier_month= CALCULATE(max('Date'[Date]),DATEADD('Date'[Date],-1,MONTH)   )
     return          
                CALCULATE (
                                         [Closing Balance Updated],
                                         FILTER ( ALLSELECTED( ( 'Date' )), 'Date'[Date] <=  earlier_month  
    
))
6

How should the funding logic be?Also formula for Closing Balance Updated?Could you please share the pbix as well? @Anurag

Because for some pattern of cashflows , the formula might seem to work, but not for all pattern, if you could share the pbix , i can have a thorough look.

If this is the funding formula,

Funding =
Var fund =IF (
HASONEVALUE ( ‘Date’[Year] ),
IF (
[CM Movement] < 0,
[CM Movement] * -1
),
CALCULATE (
SUMX (
FILTER ( VALUES ( ‘Date’[Year] ), [CM Movement] < 0 ),
[CM Movement]
)
)
)

return
if (fund<0,fund)

It will work only for the pattern given in the pbix file.

If we consider the below pattern of example, the funding Logic will be incorrect:

Example 2
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

The logic which you have mentioned,
Funding =
Var fund =IF (
HASONEVALUE ( ‘Date’[Year] ),
IF (
[CM Movement] < 0,
[CM Movement] * -1
),
CALCULATE (
SUMX (
FILTER ( VALUES ( ‘Date’[Year] ), [CM Movement] < 0 ),
[CM Movement]
)
)
)
is working in pbix because there is no funding in the pbix example, but in the above example 2 which i have mentioned it will fail.

Note, In the above example 2, for Nov 2020, the Funding is 35M , and not 70M
Using the mentioned formula, the funding will be wrongly calculated as 70M, because it is not considering the opening balance of that month for funding determination.

7

Hope I am making sense now!

8

Hi,

I attached the pbix
pls check it
check 3.pbix (241.4 KB)

9

check 3 (3).pbix (241.8 KB)
image
(I just altered the data to check) ,for Sep 2020, the Closing Balance is negative (04+0-1.2)=-0.7M
But Funding is 0 which should not be the case.
Funding should be 0.7M
Closing Balance Updated should be 0 for Sep 2020

10

Got to know that it is recursion type of logic, no idea how to implement

11

Hi,
can you share the desired output

becuze i think the below one is lacking some thing

image
image723×367 14.5 KB

  1. or this is the correct one

    image
    image847×274 15.6 KB

  2. or this one is correct

    image
    image843×185 10.4 KB

12

2
23985×1773 1.08 MB

1
14032×1794 1.34 MB

Two sample outputs expected.

13

Please be sure to check out our DAX Clean Up tool it’s a great way to ensure to make your DAX code easy to read. Thanks!

https://analysthub.enterprisedna.co/dax-clean-up

14

Hi ,
Have a look at below screen shot and let me know is it what you want.

image
image817×251 15.1 KB

15

Could you please share me the pbix? So I can test different conditions?

16

check 3 (3).pbix (243.0 KB)

17

image
image817×231 15.6 KB

We need to determine the funding based on opening balance also.
In the highlighted case, there is an opening balance of 385.9M.
Movement for the same month(May ) is -4.4M
Funding has to be determined on 385.9M-4.4M, which is positive, so no funding required in May 2021

18

Your new input causes circular condition now [closing balance updated] depend on [funding] and now funding depend on [opening Balance] and [opening Balance ] depend on [closing balance updated] .
Need to figure out how to avoid that circular loop.

19

Yea , that is the issue ! :slight_smile:

20

@Anurag Any luck with this?

21

This time i also need help not able to think how remove that circular dependency do you any idea let me know so we can work on that .