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.

Kedar_Pande's avatar
Kedar_Pande
Super User
1 year ago

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