Creating sickness absence triggers

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.