Circular dependency error

[kmax]

Hello, 

I am facing a strange issue when creating a report.
Here are the details.
 
There are two main slicers.
1- Year
2- LoB

Here are four main measures.
1- OB Initial = this is the opening balance of a year. Value is coming from a small table.

2- OB Adjusted = if column IsFirstMonth = 1, take value from [OB Initial], otherwise the Ending CARR value of previous month is the opening value of next month. e.g. if it is 2nd month, the obadjusted value will be [Ending CARR] of previous month. This is correctly getting the value of previous month as the opening value of next month.
3- Variation Ending CARR = this is the monthly sale.

4- Ending CARR =
if IsFirstMonth = 1,
[OB Initial] + [Variation Ending CARR ]
else
[OB Adjusted] + [Variation Ending CARR ]
This is where the issue is.
IF(
_IsFistMonth = 1 && SELECTEDVALUE(Activitylist[Year]) = _SelectedYear,
[OB Initial] + [Variation Ending CARR], // For the first month,
CALCULATE(
[OB Initial] + [Variation Ending CARR] // it should be [OB Adjusted] + [Variation Ending CARR ] but it is showing circular dependency error.

I have tried different ways but issue is still there.
Need help to fix this.
PBIX with dummy data can be downloaded here > https://drive.google.com/file/d/1oaD8-4JRjDGqKreyCSQjPWndl8NKwxYE/view?usp=sharing

Thanks in advance

[kmax]

Hi  v-linyulu-msft ,

Here is the updated PBIX.
https://drive.google.com/file/d/1Wk4WppXQGYzpSMcIpRiqgYMB0l9TGEHz/view?usp=sharing

Still facing issues but I have managed to get the value of previous month as the next month opening balance.

I have found two ways to tackle circular dependancy. 

Option 1 - using calculated column

The ending Balance column is working fine till 4th month.
From 5th month, the ending balance is showing wrong value.
2024-03
Ending Balance column = 1893980
2024-04
Month sale column = -65471

2024-04 Ending balance =

1893980-65471=1828509 (correct value)
but it is showing 2055416.
if we subtract
1828509-2055416 = -226907 (this is month sale value of 2024-01.
same pattern continues for rest of the months of any year.

Option 2 - using measures
from 2nd month onward, the Opening Balance adjusted value is correctly fetching from closing balance measure value of previous month.
but the closing balance measure is showing wrong answer for all months.
it is doing
opening balance + monthly variation = closing balance measure
this is fine for 1st month. From 2nd month, it should set the opening balance value from closing balance of previous month but it is using the 1st month value of opening balance for all months.

I have set Option 1 and Option 2 titles for both matrix for ease in the new PBIX.
Now, I am facing issue in getting the correct sum using both the options.

Thanks

[v-linyulu-msft · Accepted solution]

Hi,@kmax 

Regarding the issue you raised, my solution is as follows:

1.Based on the pbix file you provided, we infer that the reason you have a circular dependency problem is because you referenced [Ending CARR Measure] in your [OB Adjusted] measure, However, [OB Adjusted] is cited in [Ending CARR Measure], resulting in cyclic dependence.

Solved: Recursion in DAX - Microsoft Fabric Community

2.You can try doing the calculation in excel first and then importing it into power bi. This is by far the easiest way.

Here are links to the relevant documents:

How to create recursive functions in Excel with LAMBDA (spreadsheetweb.com)

3.You can also implement this functionality in powerquery, but you need to put a little more effort into it:

Here are links to the relevant documents:

Recursive Functions in Power Query | Power BI Data (enterprisedna.co)

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kmax]

Hi Lu,
Thanks for the details.

The requirement is to do all the calculations in PowerBI to reduce dependence on Excel. So, should I assume that this is something not possible using DAX?

[v-linyulu-msft]

Hi,@kmax 

Thank you for your quick reply.

Yes, your assumption is correct.

if your requirements involve recursion, recursion cannot be achieved with dax alone.

Of course, if you have other new discoveries, you are welcome to share with us.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly