Counting Employees with Over 30 Sick Days in R12M Using DAX

Hi Mateja ,

To achieve your goal of counting employees who have more than 30 consecutive sick days in a rolling 12-month period, your current measure needs significant refinement. This involves identifying sequences of consecutive days of sick leave and checking if any such sequence exceeds 30 days within the rolling period.

Here's a refined DAX measure tailored to your requirement:

CountEmployeesOver30ConsecutiveSickDays = 
VAR DatePeriod = MAX('Dim Dates'[Date])
VAR Last12Months =
    FILTER(
        'Fact Employee Absence',
        'Fact Employee Absence'[Absence Date FK] >= EDATE(DatePeriod, -12) &&
        'Fact Employee Absence'[Absence Date FK] <= DatePeriod &&
        RELATED('Dim Employee Wage Codes'[Absence Category]) = "Sick Leave"
    )
VAR ConsecutiveSickDays =
    ADDCOLUMNS(
        Last12Months,
        "PreviousDay",
        EARLIER('Fact Employee Absence'[Absence Date FK]) - 1
    )
VAR GroupedConsecutiveSickDays =
    ADDCOLUMNS(
        ConsecutiveSickDays,
        "SickGroup",
        RANKX(
            FILTER(
                ConsecutiveSickDays,
                'Fact Employee Absence'[DIM_EMPLOYEES_FK] = EARLIER('Fact Employee Absence'[DIM_EMPLOYEES_FK])
            ),
            'Fact Employee Absence'[Absence Date FK] - 'Fact Employee Absence'[PreviousDay]
        )
    )
VAR SickDaysPerGroup =
    SUMMARIZE(
        GroupedConsecutiveSickDays,
        'Fact Employee Absence'[DIM_EMPLOYEES_FK],
        "MaxConsecutiveDays",
        MAXX(
            FILTER(
                GroupedConsecutiveSickDays,
                'Fact Employee Absence'[DIM_EMPLOYEES_FK] = EARLIER('Fact Employee Absence'[DIM_EMPLOYEES_FK])
            ),
            COUNTROWS()
        )
    )
VAR EmployeesOver30Days =
    FILTER(
        SickDaysPerGroup,
        [MaxConsecutiveDays] > 30
    )
RETURN
COUNTROWS(EmployeesOver30Days)

The measure ensures that only absences within the last 12 months are considered by filtering dates using the EDATE function. It introduces logic to detect consecutive sick leave days by comparing each day with the previous day. Sick days are then grouped using a calculated "SickGroup," which organizes consecutive sick leave days for each employee into clusters. For each employee, the measure calculates the maximum streak of consecutive sick leave days within the rolling 12-month period. Finally, employees with streaks exceeding 30 days are identified as having long-term sickness.

To implement this measure effectively, ensure that the relationships between the Fact Employee Absence table and the dimension tables (Dim Employee Wage Codes and Dim Dates) are correctly defined. Validate that the WageHours column accurately identifies valid sick leave records. Additionally, check for any gaps in the data that might disrupt the calculation of consecutive sick leave days.

Best regards,