With a list of sick days how to calculate occurrences for each employee

[soumyaiyer]

Hello,

I have a table with a list of events and event dates for each employee. I would need help how to calculate sick days occurrences for each employee.

Workdays are from Monday - Saturday

 The only day excluded for sick is Sunday. 

1 occurence = 1 sick day

1 occurence = 2 sick days (need to include if the employee takes sick leave on Saturday & Monday which would be 2 consecutive days - Sunday not counted)

1 occurence = 3 continous days (need to include if the employee takes sick leave on Friday, Saturday & Monday OR

Saturday, Monday, Tuesday which would be 3 consecutive days - both the scenario Sunday is not counted)

Above 3 continous days is considered FMLA - no calucation needed for above 3 days.

[v-sdhruv]

Hi @soumyaiyer ,

I hope the solution provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.

Thank You for using Microsoft Community Forum

[v-sdhruv]

Hi @soumyaiyer ,
I hope the solution provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out.We would be  happy to help with any further assistance you may need.
Thank You

[v-sdhruv]

Hi @soumyaiyer ,

You can create a column to exclude Sunday

IsWorkday = IF(WEEKDAY('Table'[Event Date], 2) <> 7, 1, 0)

Then another calculated column to identify consecutive blocks

SickBlockID =
VAR CurrentDate = 'Table'[Event Date]
VAR PrevDate = 
    CALCULATE(
        MAX('Table'[Event Date]),
        FILTER(
            'Table',
            'Table'[Agent] = EARLIER('Table'[Agent]) &&
            'Table'[Event Date] < EARLIER('Table'[Event Date])
        )
    )
VAR Gap = DATEDIFF(PrevDate, CurrentDate, DAY)
RETURN
    IF(Gap <= 2, 0, 1)

Finally, a measure to calculate the occurences- 

SickOccurrences =
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        'Table',
        'Table'[IsWorkday] = 1 &&
        'Table'[SickBlockID] = 1
    )
)

This will fetch the below result-

Attached file for reference.

Hope this helps!

[soumyaiyer]

@v-sdhruv 

Thank you for helping me with this. Just a quick update, the table with the event dates does not have Sunday's included. The table log has only Dates for Monday - Saturday.

The above calcuated columns and measure is not showing 3 continous days as an occurance. 

Here is the snap shot

[v-sdhruv]

Hi @soumyaiyer ,

Since you wanted Sundays not to be counted, I created a measure to exclude sundays. But if you wanted it to be included, you can modify the measure:

IsWorkday = IF(WEEKDAY('Table'[Event Date], 2) <> 7, 1, 1)

This will return 1 for all days

Hope this helps! 

[soumyaiyer]

Hi @v-sdhruv 

Thank you for the quick response, after making the changes for the workday measure.

The occurrences are not calculating for all 2 or 3 consecutive days . Please see below. 

[v-sdhruv]

Are there any filters to the page which might be restricting the occurences?

[soumyaiyer]

Hi,

No there are no filter set for the visual or the page. 

[soumyaiyer]

Not sure whether the DATEDIFF and Gap calcuation is causing not to pick the correct number of occurences

SickBlockID =
VAR CurrentDate = 'Table'[Event Date]
VAR PrevDate = 
    CALCULATE(
        MAX('Table'[Event Date]),
        FILTER(
            'Table',
            'Table'[Agent] = EARLIER('Table'[Agent]) &&
            'Table'[Event Date] < EARLIER('Table'[Event Date])
        )
    )
VAR Gap = DATEDIFF(PrevDate, CurrentDate, DAY)
RETURN
    IF(Gap <= 2, 0, 1)

[v-sdhruv]

Hi @soumyaiyer ,

 IF(Gap <= 2, 0, 1)

is only evaluated if  Gap is not blank.

Try : IF(ISBLANK(Gap) || Gap > 2, 1, 0)

Hope this helps!

[soumyaiyer]

@v-sdhruv 

Thank you, I would need help to filter the event by sick , please advise to modify the measure.

NEW SickOccurrences =

CALCULATE(

    COUNTROWS('Attendance Tracker'),

    FILTER(

        'Attendance Tracker',

        'Attendance Tracker'[IsWorkday] = 1 &&

        'Attendance Tracker'[SickBlockID] = 1

    )

)

===============================================

SickBlockID =

VAR CurrentDate = 'Attendance Tracker'[Event Date]

VAR PrevDate =

    CALCULATE(

        MAX('Attendance Tracker'[Event Date]),

        FILTER(

            'Attendance Tracker',

            'Attendance Tracker'[Agent Name.title] = EARLIER('Attendance Tracker'[Agent Name.title]) &&

            'Attendance Tracker'[Event Date] < EARLIER('Attendance Tracker'[Event Date])

        )

    )

VAR Gap = DATEDIFF(PrevDate, CurrentDate, DAY)

RETURN

    IF(ISBLANK(Gap) || Gap > 2, 1, 0)

[v-sdhruv · Accepted solution]

Hi @soumyaiyer ,

You can try anothe measure which will count the occurences of sick days

NEW SickOccurrences =
CALCULATE(
COUNTROWS('Attendance Tracker'),
FILTER(
'Attendance Tracker',
'Attendance Tracker'[IsWorkday] = 1 &&
'Attendance Tracker'[SickBlockID] = 1 &&
'Attendance Tracker'[Event Type] = "Sick"
)
)

 Hope this helps!

[soumyaiyer]

@v-sdhruv 

Thank you so much for guiding me and helping me through this. I am learning 🙂 I applied the above measure and the results was same. I scrubbed through the measures created again and added a filter in the SickBlockID column and it worked!! Many thanks again! Have a blessed. I will accept the soultion and reach out if need with any other task. 

'Attendance Tracker'[Event Type] = "Sick"

[soumyaiyer]

[soumyaiyer]

Agent	Event	Event Date	Supervisor
Miriam	Sick	1/10/2025 12:00:00 AM	Kimberley
Abigail	Sick	1/10/2025 12:00:00 AM	Kimberley
Dereyka	Sick	1/11/2024 12:00:00 AM	Derrick
Miriam	Sick	1/11/2025 12:00:00 AM	Kimberley
Miriam	Sick	1/13/2025 12:00:00 AM	Kimberley
Jason	Sick	1/13/2025 12:00:00 AM	Robin
Jason	Sick	1/14/2025 12:00:00 AM	Robin
Angela	Sick	1/15/2025 12:00:00 AM	Robin
Jason	Sick	1/15/2025 12:00:00 AM	Robin
Charron	Sick	1/15/2025 12:00:00 AM	Kimberley
Angela	Sick	1/16/2025 12:00:00 AM	Robin
Charron	Sick	1/16/2025 12:00:00 AM	Kimberley
Angela	Sick	1/17/2025 12:00:00 AM	Robin
JianCarlo	Sick	1/17/2025 12:00:00 AM	Kimberley
Charron	Sick	1/17/2025 12:00:00 AM	Kimberley
JianCarlo	Sick	1/18/2025 12:00:00 AM	Kimberley
Brenda	Sick	1/2/2025 12:00:00 AM	Robin
JianCarlo	Sick	1/20/2025 12:00:00 AM	Kimberley
Abigail	Sick	1/22/2024 12:00:00 AM	Kimberley
Dereyka	Sick	1/22/2025 12:00:00 AM	Derrick
Brenda	Sick	1/23/2025 12:00:00 AM	Robin
Agnes	Sick	1/23/2025 12:00:00 AM	Arthur
Jamie	Sick	1/23/2025 12:00:00 AM	Arthur
Jason	Sick	1/24/2024 12:00:00 AM	Robin
Brenda	Sick	1/24/2025 12:00:00 AM	Robin
Jamie	Sick	1/24/2025 12:00:00 AM	Arthur
Jason	Sick	1/25/2024 12:00:00 AM	Robin
Angela	Sick	1/26/2024 12:00:00 AM	Robin
Jason	Sick	1/26/2024 12:00:00 AM	Robin
Krashaun	Sick	1/27/2025 12:00:00 AM	Arthur
Brittany	Sick	1/28/2025 12:00:00 AM	Elaine
Nora	Sick	1/28/2025 12:00:00 AM	Kimberley
Angela	Sick	1/29/2024 12:00:00 AM	Robin
Charron	Sick	1/29/2024 12:00:00 AM	Kimberley
Nora	Sick	1/29/2025 12:00:00 AM	Kimberley
JianCarlo	Sick	1/3/2024 12:00:00 AM	Kimberley
Nora	Sick	1/3/2024 12:00:00 AM	Kimberley
Kristin	Sick	1/3/2025 12:00:00 AM	Elaine
Kat	Sick	1/3/2025 12:00:00 AM	Raymond
Brenda	Sick	1/3/2025 12:00:00 AM	Robin
Charron	Sick	1/30/2024 12:00:00 AM	Kimberley
Nora	Sick	1/30/2025 12:00:00 AM	Kimberley
JianCarlo	Sick	1/4/2024 12:00:00 AM	Kimberley
Nora	Sick	1/4/2024 12:00:00 AM	Kimberley
David	Sick	1/4/2025 12:00:00 AM	Derrick
Kat	Sick	1/4/2025 12:00:00 AM	Raymond
JianCarlo	Sick	1/5/2024 12:00:00 AM	Kimberley
Nora	Sick	1/5/2024 12:00:00 AM	Kimberley
Ronniqua	Sick	1/6/2025 12:00:00 AM	Derrick
Kat	Sick	1/6/2025 12:00:00 AM	Raymond
Ronniqua	Sick	1/7/2025 12:00:00 AM	Derrick
Brenda	Sick	1/8/2024 12:00:00 AM	Robin
Ronniqua	Sick	1/8/2025 12:00:00 AM	Derrick
Dereyka	Sick	1/8/2025 12:00:00 AM	Derrick
Abigail	Sick	1/9/2025 12:00:00 AM	Kimberley
Mariela	Sick	1/9/2025 12:00:00 AM	Arthur
Dereyka	Sick	1/9/2025 12:00:00 AM	Derrick
David	Sick	10/1/2024 12:00:00 AM	Derrick
Gwendolyn	Sick	10/11/2024 12:00:00 AM	Kimberley
Mariela	Sick	10/18/2024 12:00:00 AM	Arthur
Dereyka	Sick	10/18/2024 12:00:00 AM	Derrick
Aleisha	Sick	10/21/2024 12:00:00 AM	Arthur
Dereyka	Sick	10/21/2024 12:00:00 AM	Derrick
Aleisha	Sick	10/22/2024 12:00:00 AM	Arthur
Dereyka	Sick	10/22/2024 12:00:00 AM	Derrick
Brittany	Sick	10/23/2024 12:00:00 AM	Elaine
Aleisha	Sick	10/23/2024 12:00:00 AM	Arthur
David	Sick	10/24/2024 12:00:00 AM	Derrick
LaTronika	Sick	10/28/2024 12:00:00 AM	Kimberley
JianCarlo	Sick	10/30/2024 12:00:00 AM	Kimberley
Rafael	Sick	10/30/2024 12:00:00 AM	Raymond
JianCarlo	Sick	10/31/2024 12:00:00 AM	Kimberley
LaTronika	Sick	10/31/2024 12:00:00 AM	Kimberley
Rafael	Sick	10/31/2024 12:00:00 AM	Raymond

[FBergamaschi]

We can try to help you but 

Please include, in a usable format, not an image, a small set of rows for each of the tables involved in your request and show the data model in a picture, so that we can import the tables in Power BI and reproduce the data model. The subset of rows you provide, even is just a subset of the original tables, must cover your issue or question completely. Alternatively, you can share your .pbix via some cloud service and paste the link here. Do not include sensitive information and do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided and make sure, in case you show a Power BI visual, to clarify the columns used in the grouping sections of the visual.

Need help uploading data? click here

Want faster answers? click here