Creating sickness absence triggers

[Andy2410]

Hello

I have already created an Absence Dashboard for our HR and Leadership teams to use, but one of the outstanding things for me to add is a sickness absence trigger section that shows individuals that have hit the two respective absence triggers and can be RAGed to show those that have hit the triggers or are close to hitting them.

Absence triggers are 3 absences in 6 months and 2 absences (over 20 working days) in 12 months. 

I am struggling to make this work and wondered if anyone has done something similar before and could share their experience.

Employee Data held in 'Reporting DimPerson' table

Absence Data (incl. reason, start and end date etc) held in 'Reporting DimAbsence' table

No. of occurences/days absent held in 'Reporting FactAbsenceMonthly' table

The Person and Absence dimension tables are joined to the fact table in the normal way

Essentially I need a table where the number of occurences in a 6 or 12 month window is summed up based on the individual employee, so one employee has one line with a number at the end (I already have a table set up that shows each absence on a separate line in those periods)

If you need any further information please let me know and thanks in advance

[bhanu_gautam]

@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.

[Andy2410]

Ok I am nearly there with your suggested solution, one thing that I didn't mention is that I need to have two distinct RAG statuses for the triggers, so the HR team need to know if the individual has triggered the 6 month or the 12 month trigger, is there a way to do this so that I can then create two tables on the final dashboard, one showing the individuals who have his the 3in6month trigger and a separate table showing 2in12 triggers?

Many thanks again

[bhanu_gautam]

@Andy2410 To create the distinct RAG statuses for the 6-month and 12-month triggers and display them in separate tables

Absences in the Last 6 Months:
DAX
AbsencesLast6Months =
CALCULATE(
COUNTROWS('Reporting DimAbsence'),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -6, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)
Absences in the Last 12 Months:
DAX
AbsencesLast12Months =
CALCULATE(
COUNTROWS('Reporting DimAbsence'),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)
Total Days Absent in the Last 12 Months:
DAX
TotalDaysAbsentLast12Months =
CALCULATE(
SUM('Reporting FactAbsenceMonthly'[DaysAbsent]),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH),
'Reporting DimAbsence'[AbsenceType] = "Sickness"
)
6-Month Trigger:
DAX
Trigger6Months = IF([AbsencesLast6Months] >= 3, 1, 0)
12-Month Trigger:
DAX
Trigger12Months = IF([AbsencesLast12Months] >= 2 && [TotalDaysAbsentLast12Months] > 20, 1, 0)
6-Month RAG Status:
DAX
RAGStatus6Months =
SWITCH(
TRUE(),
[Trigger6Months] = 1, "Red",
"Green"
)
12-Month RAG Status:
DAX
RAGStatus12Months =
SWITCH(
TRUE(),
[Trigger12Months] = 1, "Red",
"Green"
)
Summary Table for 6-Month Trigger:
DAX
SummaryTable6Months =
SUMMARIZE(
'Reporting DimPerson',
'Reporting DimPerson'[EmployeeID],
'Reporting DimPerson'[EmployeeName],
"AbsencesLast6Months", [AbsencesLast6Months],
"RAGStatus6Months", [RAGStatus6Months]
)
Summary Table for 12-Month Trigger:
DAX
SummaryTable12Months =
SUMMARIZE(
'Reporting DimPerson',
'Reporting DimPerson'[EmployeeID],
'Reporting DimPerson'[EmployeeName],
"AbsencesLast12Months", [AbsencesLast12Months],
"TotalDaysAbsentLast12Months", [TotalDaysAbsentLast12Months],
"RAGStatus12Months", [RAGStatus12Months]
)
These changes ensure that you have two distinct RAG statuses for the 6-month and 12-month triggers and can display them in separate tables on your dashboard.

[Andy2410]

Thank you for coming back to me with the additional DAX. I have added all of this in, but have come up against a new problem. Everyone past and present comes up flagged as "Red" for the 6 Month trigger even when they have no sickness absences. Not sure what I've done wrong. Appreciate your thoughts.

[Andy2410]

Thank you, I will have a look at this after lunch