Circular dependency

Hi brankocareer ，

using CALCULATE in calculated column and without proper handling on context transition can be very tricky. 

in your case, Partial End by Day and Total End by Day are depending on each other. 

To solve this issue, try:

1) add index column in your 'FTE View' table, rule of thumb!

2) or provide a sample of the 'FTE View' table, with expected result. Expressions like CALCUALTE([], ALLEXCEPT()) may help do the trick. 

Hi brankocareer 

As per my understanding, Removing a filter or condition changes the dependency chain, causing a loop when creating calculated columns. Convert both Partial End by Day and Total End by Day into measures.

Example of Partial End by Day 

Partial End by Day Measure =
VAR _EndDate = MAX('FTE View'[End Date])
VAR _PayDaysAfterEnd =
    CALCULATE(
        COUNT('Date'[Pay Day]),
        'Date'[Date] >= DATE(YEAR(_EndDate), MONTH(_EndDate), 1),
        'Date'[Date] <= _EndDate,
        'Date'[Pay Day] <> 1,
        'Date'[Pay Day] <> 7,
        'Date'[Pay Day] <> 8,
        'Date'[Pay Day] <> 14
    )
VAR _EndSalary =
    CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status] = "Filled") * _PayDaysAfterEnd
RETURN
    _EndSalary

Example of Total End by Day 

Total End by Day Measure =
VAR _EndDate = MAX('FTE View'[End Date])
VAR _PayDaysAfterEnd =
    CALCULATE(
        COUNT('Date'[Pay Day]),
        'Date'[Date] >= DATE(YEAR(_EndDate), MONTH(_EndDate), 1),
        'Date'[Pay Day] <> 1,
        'Date'[Pay Day] <> 7,
        'Date'[Pay Day] <> 8,
        'Date'[Pay Day] <> 14
    )
VAR _EndSalary =
    CALCULATE(SUM('FTE View'[Daily Rate]), 'FTE View'[Position Status] = "Filled") * _PayDaysAfterEnd
RETURN
    _EndSalary

 Use these measures in visuals or further calculations.

By switching to measures, dependencies are recalculated dynamically, avoiding circular references.

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Please Subscribe my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS