Counting Employees with Over 30 Sick Days in R12M Using DAX

[Mateja]

Hello Power BI Community,

I'm currently working on a DAX measure to count the number of employees who have more than 30 sick days in a rolling 12-month period (R12M). However, I'm encountering issues where the measure does not return the expected results. I would appreciate any guidance.

Data Model Details:

Fact Table: Fact Employee Absence

Contains columns such as Absence Date FK, DIM_EMPLOYEES_FK, and WageHours.
DIM_EMPLOYEES_FK is connected to Dim Employees via DIM_EMPLOYEE_KEY.

Dimension Table: Dim Employee Wage Codes

Contains the Absence Category column.

Dimension Table: Dim Dates

Used for date filtering.

Objective:

I want to count employees who have accumulated more than 30 sick days within the last 12 months. The Absence Category for sick leave is stored in the Dim Employee Wage Codes table.

Current Measure:

CountEmployeesOver30SickDays =
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 EmployeeSickDays =
SUMMARIZE(
Last12Months,
'Fact Employee Absence'[DIM_EMPLOYEES_FK],
"TotalSickDays",
CALCULATE(
DISTINCTCOUNT('Fact Employee Absence'[Absence Date FK]),
'Fact Employee Absence'[WageHours] > 0
)
)
VAR EmployeesOver30Days =
FILTER(
EmployeeSickDays,
[TotalSickDays] > 30
)
RETURN
COUNTROWS(EmployeesOver30Days)

The measure is not returing the correct number and is severly undercounting number of employees. 

The point is actually to have a measure that will count numbers of employees who in the R12m have had more than 30  consecutive days of reported illness to see how many are actually long-term sick. IF you can directly help with this measure I would be greatful 🙂

[v-jiewu-msft · Accepted solution]

Hi ,

Based on the testing, creating the sample table.

Then, using the following DAX formula to create new measure.

CountEmployeesOver30SickDays = 
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 EmployeeSickDays =
SUMMARIZE(
Last12Months,
'Fact Employee Absence'[DIM_EMPLOYEES_FK],
"TotalSickDays",
CALCULATE(
DISTINCTCOUNT('Fact Employee Absence'[Absence Date FK]),
'Fact Employee Absence'[WageHours] > 0
)
)
VAR EmployeesOver30Days =
FILTER(
EmployeeSickDays,
[TotalSickDays] > 30
)
RETURN
COUNTROWS(EmployeesOver30Days)

The result is shown below. In the sample data, two employees are sick for more than 30 days.

Besides, check if any filter has been applied to the employee absence table.

Best Regards,

Wisdom Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Mateja]

Hi,

I have accepted it as a solution, it does calculate the number of employees having more than 30 days sick in last 12 months. Thank you 🙂

[tamerj1]

Hi @Mateja 

please try

CountEmployeesOver30SickDays =
VAR DateEnd =
MAX ( 'Dim Dates'[Date] )
VAR DateStart =
EDATE ( DateEnd, -11 )
VAR EmployeeAbsence =
CALCULATETABLE (
SUMMARIZE (
'Fact Employee Absence',
'Fact Employee Absence'[Absence Date FK],
"@Count", COUNTROWS ( 'Fact Employee Absence' )
),
'Dim Dates'[Date] >= DateStart,
'Dim Dates'[Date] <= DateEnd,
ALL ( 'Dim Dates' ),
KEEPFILTERS ( 'Dim Employee Wage Codes'[Absence Category] = "Sick Leave" ),
KEEPFILTERS ( 'Fact Employee Absence'[WageHours] > 0 )
)
VAR Result =
COUNTROWS ( FILTER ( EmployeeAbsence, [@Count] > 30 ) )
RETURN
Result

[Poojara_D12]

Hi @Mateja 

Here’s the core DAX logic to achieve this:

CountEmployeesOver30ConsecutiveSickDays =
VAR Last12Months =
    FILTER(
        'Fact Employee Absence',
        'Fact Employee Absence'[Absence Date FK] >= EDATE(MAX('Dim Dates'[Date]), -12) &&
        RELATED('Dim Employee Wage Codes'[Absence Category]) = "Sick Leave"
    )
VAR ConsecutiveGroups =
    // Logic to calculate streaks and group them
VAR EmployeeMaxConsecutive =
    // Logic to calculate the max streak for each employee
RETURN
COUNTROWS(
    FILTER(EmployeeMaxConsecutive, [MaxConsecutiveDays] > 30)
)

The measure counts employees who had more than 30 consecutive sick days in the last 12 months.

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
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS 

[DataNinja777]

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,

[Mateja]

Hi, 

Thank you for taking the time to answer and describe the logic. This help to understand how the steps must be set up in order for measure to function. 

When trying to utilise the measure the error comes at line 14 for var

VAR ConsecutiveSickDays =

 'EARLIER/EARLIEST refers to an earlier row context which doesn't exist.'

How can we work around this error? I understand that the Last12Month table contains that filter context because it is a filtered table from Fact Absence but I do not know how to resolve the issue.

[Kedar_Pande]

@Mateja 

You can try:

CountEmployeesOver30SickDays =
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 ContinuousSickPeriods =
ADDCOLUMNS(
Last12Months,
"PreviousAbsenceDate", 
CALCULATE(MAX('Fact Employee Absence'[Absence Date FK]), 
FILTER(Last12Months, 
'Fact Employee Absence'[DIM_EMPLOYEES_FK] = EARLIER('Fact Employee Absence'[DIM_EMPLOYEES_FK]) &&
'Fact Employee Absence'[Absence Date FK] < EARLIER('Fact Employee Absence'[Absence Date FK]))),
"ConsecutiveDays", 
DATEDIFF(
CALCULATE(MAX('Fact Employee Absence'[Absence Date FK]), 
FILTER(Last12Months, 
'Fact Employee Absence'[DIM_EMPLOYEES_FK] = EARLIER('Fact Employee Absence'[DIM_EMPLOYEES_FK]))),
'Fact Employee Absence'[Absence Date FK],
DAY
)
)
VAR EmployeeSickDays =
SUMMARIZE(
ContinuousSickPeriods,
'Fact Employee Absence'[DIM_EMPLOYEES_FK],
"TotalConsecutiveSickDays",
MAXX(FILTER(ContinuousSickPeriods, [ConsecutiveDays] > 0), [ConsecutiveDays])
)
VAR EmployeesOver30Days =
FILTER(
EmployeeSickDays,
[TotalConsecutiveSickDays] > 30
)
RETURN
COUNTROWS(EmployeesOver30Days)

💌 If this helped, a Kudos 👍 or Solution mark ✅ would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn