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
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 - Proud to be a Super User
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS
