Counting Employees with Over 30 Sick Days in R12M Using DAX

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.