Forum Discussion

Andy2410's avatar
Andy2410
Frequent Visitor
1 year ago

Creating sickness absence triggers

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 individu...
Need Help
bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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's avatar
Andy2410
Frequent Visitor
1 year ago

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's avatar
    bhanu_gautam
    Super User
    1 year ago

    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's avatar
      Andy2410
      Frequent Visitor
      1 year ago

      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.