Forum Discussion

Mateja's avatar
Mateja
Helper II
1 year ago
Solved

Counting Employees with Over 30 Sick Days in R12M Using DAX

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 issue...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

Poojara_D12's avatar
Poojara_D12
Super User
1 year ago

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