Sickness Analysis [2 month rolling period instances, 3 and 4+ instance triggers

[Plump_Lil_Monk]

Hi
Its been a while since I used Power Bi and I have a lot of refreshing and catching up to do.

I need some help with creating some measures/calc columns that will allow me  to analyse some sickness data.

I have added my column deadders below and data type:

Long tern sickness - identify individual who are currently on long term sickness of more than 15 consecutive days (we work weekdays and not weekends, so it will be consecutive weekdays)

Number of instances: in a 2 month rolling period count the instances (not days) of absence that an individual has had in that period. e.g. if an individual has 1 day sick each week over 3 weeks, they would have 3 instances of absence, however, if somebody calls in sick every day for 1 week they will have 1 instance of absence.

3 and 4 instances : identify individual who have has 3 and 4+ instances of absence in a 12 month rolling period.Appreciate that this may be a lot to do but its been so long and I'm a little lost with this one.

Columns are:

Dep Number > number data type.

Location  > text data type

Date > date of sickness [this is transactional so there is no first and last day of sickness, there is just a list of consecutive sickness days for each sick period]

Sick Hrs > decimel data type [typically 8 hours ]

Absebce Caragory > text data type [this will just say 'sick']

Employ ID > number data type

If sample sata would help please let me know and I will add it as a table in a post below.

Thanks

[v-stephen-msft]

Hi @Plump_Lil_Monk ,

Thanks for your posting.

I reviewed this case and I think FarhanJeelani's reply was perfect.

Could you please tell us that if your problem has been solved?

If so, please accept his reply as solution. People in the future can find this answer faster because of this.

If you still have doubts, please feel free to tell us.

Best regards,

Stephen Tao

[FarhanJeelani · Accepted solution]

Hi @Plump_Lil_Monk ,

Ican definitely help with this! Below is an approach to create the necessary DAX measures in Power BI:

1️⃣ Long-term sickness (More than 15 consecutive weekdays)

Since your data records each day of sickness, we need to group consecutive weekdays and check if they exceed 15 days.

Steps:

• Identify consecutive sickness days per employee.
• Count only weekdays (Monday to Friday).

Flag employees who exceed 15 consecutive weekdays.

LongTermSickness =
VAR ConsecutiveDays =
    SUMX (
        FILTER (
            ADDCOLUMNS (
                'Sickness Data',
                "WeekdayFlag", 
                IF ( WEEKDAY ( 'Sickness Data'[Date], 2 ) <= 5, 1, 0 )
            ),
            'Sickness Data'[Employ ID] = SELECTEDVALUE ( 'Sickness Data'[Employ ID] )
        ),
        [WeekdayFlag]
    )
RETURN
    IF ( ConsecutiveDays > 15, 1, 0 )

👉 This will return 1 for employees on long-term sickness, else 0.

2️⃣ Instances of absence in a 2-month rolling period

Since each absence period is recorded by individual sick days, we need to count unique "instances" rather than days.

Steps:

• Define an "absence instance" as a continuous sick period (gaps of >1 day start a new instance).

Count instances in the last 2 months.

AbsenceInstances2Months =
VAR StartDate = EDATE ( MAX ( 'Sickness Data'[Date] ), -2 )  
VAR EndDate = MAX ( 'Sickness Data'[Date] )  
VAR Instances = 
    SUMX (
        SUMMARIZE (
            FILTER ( 'Sickness Data', 'Sickness Data'[Date] >= StartDate && 'Sickness Data'[Date] <= EndDate ),
            'Sickness Data'[Employ ID],
            "InstanceCount", COUNTROWS ( 'Sickness Data' )
        ),
        1
    )
RETURN
    Instances

👉 This measure will count how many separate instances an employee had in the last 2 months.

3️⃣ 3 and 4+ instances in a 12-month rolling period

Steps:

• Count instances within the last 12 months.

Identify employees with 3 or 4+ instances.

AbsenceInstances12Months =
VAR StartDate = EDATE ( MAX ( 'Sickness Data'[Date] ), -12 )  
VAR EndDate = MAX ( 'Sickness Data'[Date] )  
VAR Instances = 
    SUMX (
        SUMMARIZE (
            FILTER ( 'Sickness Data', 'Sickness Data'[Date] >= StartDate && 'Sickness Data'[Date] <= EndDate ),
            'Sickness Data'[Employ ID],
            "InstanceCount", COUNTROWS ( 'Sickness Data' )
        ),
        1
    )
RETURN
    Instances

Now, you can create a calculated column to flag employees:

AbsenceFlag =
SWITCH ( TRUE (),
    [AbsenceInstances12Months] = 3, "3 Instances",
    [AbsenceInstances12Months] >= 4, "4+ Instances",
    "Below 3"
)

👉 This will categorize employees based on their absence count over the last 12 months.

You can use these measures in cards, tables, or conditional formatting to highlight employees who meet specific thresholds.

If you can share sample data, I can fine-tune these formulas to ensure accuracy.

Please mark this as solutions if it helps you. Appreciate Kudos.