Absenteeism rate: formula and example.

WITH months AS (
  SELECT generate_series(DATE '2025-01-01', DATE '2025-12-01', interval '1 month')::date AS month
),
available AS (
  SELECT m.month, SUM(e.fte) * 21 AS fte_days  -- assumption: 21 working days per month
  FROM months m
  JOIN employees e ON e.hire_date < m.month + interval '1 month'
   AND (e.termination_date IS NULL OR e.termination_date >= m.month)
  GROUP BY m.month
),
sick AS (
  SELECT date_trunc('month', start_date)::date AS month, SUM(sick_days) AS sick_days
  FROM absences
  GROUP BY 1
)
SELECT a.month, ROUND(100.0 * COALESCE(s.sick_days, 0) / NULLIF(a.fte_days, 0), 1) AS absenteeism_pct
FROM available a
LEFT JOIN sick s ON s.month = a.month
ORDER BY a.month;

Sick Days = SUM ( Absences[SickDays] )
Absenteeism % =
VAR d = MAX ( 'Date'[Date] )
VAR ActiveFTE = SUMX ( FILTER ( ALL ( Employees ), Employees[HireDate] <= d && ( ISBLANK ( Employees[TerminationDate] ) || Employees[TerminationDate] > d ) ), Employees[FTE] )
VAR Workdays = CALCULATE ( COUNTROWS ( 'Date' ), 'Date'[IsWorkday] = TRUE () )
RETURN DIVIDE ( [Sick Days], ActiveFTE * Workdays )