Calculated Column To Determine Attendance Status

Hi  Anonymous

 

What I can see, the issue might be that your SWITCH(TRUE()) logic is evaluating the hours before checking the special day types like Holiday, Weekend, etc. So even if it should be marked as “Holiday” or “Leave,” it’s defaulting to “Absent” too early.

One approach that’s worked for me in the past is to check all the special conditions first (like holidays, weekends, approved leave, etc.), and only then check the hours worked.

 

Here’s a cleaner structure you could try:

 

Attendance Status =
VAR Hrs = COALESCE('Attendance'[Completed Hours], 0)
VAR IsHoliday = RELATED('Calendar'[HolidayFlag]) = 1
VAR IsWeekend = RELATED('Calendar'[WeekendFlag]) = 1
VAR NotReq = RELATED('Calendar'[NotRequiredFlag]) = 1
VAR IsLeave = RELATED('Leave'[ApprovedLeaveFlag]) = 1
VAR IsSick = RELATED('Sickness'[SickFlag]) = 1

RETURN
SWITCH (
    TRUE(),
    NotReq, "Not Required",
    IsHoliday, "Holiday",
    IsWeekend, "Weekend",
    IsLeave, "Leave",
    IsSick, "Sick",
    ISBLANK('Attendance'[Completed Hours]), "No Time",
    Hrs >= 7.5, "Present",
    Hrs >= 4, "Half Day",
    Hrs > 0, "Partial",
    "Absent"
)

This structure checks all special cases first and only then evaluates based on hours. It also handles blanks more safely using COALESCE.

 

One thing to double-check make sure the relationships to your Calendar and other lookup tables are active and working as expected