Creating sickness absence triggers

Andy2410 

Create a Date Table: Ensure you have a date table in your model to handle date calculations.

Create a measure to count the number of absences in the last 6 months for each employee.
DAX
AbsencesLast6Months =
CALCULATE(
COUNTROWS('Reporting DimAbsence'),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)

Calculate Absences in the Last 12 Months:
Create a measure to count the number of absences in the last 12 months for each employee.
AbsencesLast12Months =
CALCULATE(
COUNTROWS('Reporting DimAbsence'),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)

Create a measure to sum the total days absent in the last 12 months for each employee.
DAX
TotalDaysAbsentLast12Months =
CALCULATE(
SUM('Reporting FactAbsenceMonthly'[DaysAbsent]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)

Create Trigger Flags:
Create measures to flag if an employee has hit the absence triggers.
DAX
Trigger6Months = IF([AbsencesLast6Months] >= 3, 1, 0)
Trigger12Months = IF([AbsencesLast12Months] >= 2 && [TotalDaysAbsentLast12Months] > 20, 1, 0)

Create a measure to combine the triggers into a single RAG status.
DAX
AbsenceTriggerStatus =
SWITCH(
TRUE(),
[Trigger6Months] = 1 && [Trigger12Months] = 1, "Red",
[Trigger6Months] = 1 || [Trigger12Months] = 1, "Amber",
"Green"
)

Create a Summary Table:
Create a summary table to show each employee with their absence trigger status.
DAX
SummaryTable =
SUMMARIZE(
'Reporting DimPerson',
'Reporting DimPerson'[EmployeeID],
'Reporting DimPerson'[EmployeeName],
"AbsencesLast6Months", [AbsencesLast6Months],
"AbsencesLast12Months", [AbsencesLast12Months],
"TotalDaysAbsentLast12Months", [TotalDaysAbsentLast12Months],
"AbsenceTriggerStatus", [AbsenceTriggerStatus]

Use the summary table to create a visual in Power BI that shows each employee and their absence trigger status.