Circular dependency

[melg-9]

I have access to some data which I do not own and have connected to their dataset, but cannot make any changes to it. 

Relationship is as follows:

I am trying to work out a count for the number of staff that have over 20 days annual leave.

I have tried creating a calculated column which works fine and I get the correct answer. 

 

However, as soon as I try do this for the next quarter, I receive a circular dependency.

Below are some more details about how the measures are calculated (I highlighted each one in a separate colour so it was easier to match).

FY23 Q3 Forecasted Balance (30JUN23) = '_Measures Table'[FY23 Q2 Forecasted Balance (31MAR23)] + '_Measures Table'[FY23 Q3 Accrual Days] - '_Measures Table'[FY23 Q3 Leave Days Ending]

FY23 Q2 Forecasted Balance (31MAR23) = '_Measures Table'[FY23 Q1 Forecasted Balance (31DEC22)] + '_Measures Table'[FY23 Q2 Accrual Days] - '_Measures Table'[FY23 Q2 Leave Days Ending]

FY23 Q3 Accrual Days = SUM('Leave Balances'[_FY23 Q3 Employee Accrual])

FY23 Q3 Leave Days Ending = SUM('Leave Details'[_FY23 Q3 Leave Days])

If someone can please help me avoid this circular dependency and lead me in the right direction that would be appreciated. Thanks.

[v-zhangti]

Hi, @melg-9 

A  circular dependency is when a measure/column A uses another B to compute and B need A to have a result. 

Please refer to the following links in the hopes of helping you.
Understanding circular dependencies in DAX - SQLBI

Best Regards,

Community Support Team _Charlotte

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