Counting weekend days as sick days if Friday and Monday were sick days

[Mateja]

Hi,

I have a small challenge where I need to figure out how to count Saturday and Sunday as a Sick days if person was sick Friday and Monday.

I have a measure:

IsFullTimeSick =

VAR IllnessDate = 'F_illness'[Date]

VAR IllnessWeekDay = WEEKDAY(IllnessDate)   -- Get the day of the week (Sunday = 1, Monday = 2, ..., Saturday = 7)

VAR IsWeekend = IllnessWeekDay = 1 || IllnessWeekDay = 7   -- Check if the 'IllnessDate' is a Saturday or Sunday

VAR NormalWorkingHours =

    IF(

        NOT IsWeekend,

        MAXX(

            FILTER(D_WorkSchedule, D_WorkSchedule[Day] = UPPER(FORMAT(IllnessDate, "dddd"))),

            D_WorkSchedule[Hours]

        ),

        0   -- weekend hours are not present, normal working hours are 0 (no need to consider it as full-time sick)

    )

VAR IsFullTime = 'F_illness'[IllnessHours] >= NormalWorkingHours

RETURN

    IF(

        NOT IsWeekend,

        IF(

            IsFullTime,

            1, -- Flag for full-time sick leave (1).

            0  -- Flag for non-sick (0).

        ),

        0   -- For weekends, set the value to 0

    )

This measure crosschecks the illness hours against the regular working schedule (MON to FRI) and checks if a person was sick the whole working day. If yes, then 1

NOw based on the results from this Calculated column I need a measure that will count weekends as sick days if Friday and  Monday were sick days.
Visible from the picture 10 and 11/ 06 should be counted each as 1/sick day because  Fri and Mon are sick days 

So I need a measure that will calculate weekends as sick days if the days around the weekend are sick days.

I also need help with the public holidays.
I have a table with public holidays with two columns Name and date.
I need to count public holidays as sick day if a person was sick the day before and the day after.

Thank you!

[Anonymous]

Hi @Mateja ,

If I understand correctly, it seems that you want to get the count of sick days. And it include the following conditions:

• If the weekend is before or after sick days, then that weekend should include in sick days
• If the public holiday is before or after sick days, then that public holiday should include in sick days

Could you please provide some raw data in the table 'F_illness', 'D_WorkSchedule' and 'public holiday' table

(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following links to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Mateja]

Hi v-yiruan-msft,

F_illness data: can be found in reply under this one.

Public holiday table in form of querry:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdhNb9NgDAfwrxL1vEnPS563I7DBaQgJJITGDhGLWCWWSE0L7NuT2sNbEtuJ1NvfSdv8asfp7e3OGWcujR1fu4vdx/ZP9a1tDt9Pxrg0VFfN0+7u4n9RfWnKWHTTnLr7p+rLw+kw3M8KrBkLPvT9ffX+sB/Dapq6Mb1uhmN7qD6PJ5kf7F/im76bHR7wI9rhWN1MDhyDfH7XtmsPza/q06F5Gk9wNatx54PfDD/abtj33SL25/jrw/44zD9XxPc9Z4/9Mgtj9q7vhuP+eDouzmzdpavPFQ+H/XB8bIbq+nc7jcMkXhwdx/ht/3ff/Xyd2S1kFsisTIYFTiDDtJbIMA4iGeTeLMksWcpkUAO/B5YMYudZMsxqlsyuklmdzOpkViFzW8gc9EEtk2FBEMgwTRIZxlkkc1KXOeKQyaAGvjtL5ujSL8kwiyyZWyVzOpnTyZxC5reQeeiDKJNhQRLIPM1VlszTVOXJvESGQVDJoAZ+DywZxC6zZJgVlsyvknmdzOtkXiGrt5CNRR6/mECGBUUggxRuGCxZTWOXJ4McPv6MrN4wGLGmSGQQ28KSQeYMS1avktU6Wa2T1QpZ2EIWaLIJZIFmG0cGqTMSGcYyWZC6DAIbVbJArcKSBbj0fJdhxndZWCULOlnQyYJCFreQRdouBDIs8AJZnO8XU7JIY5cnixIZBHCLlcki7RAsGcb8vSxO7nNTsvgCIpBFxuQVWSQTliwRy5IswUgLOhkWKfeyRJOTI0s0NlmyBF0mL/lJIsNA77JEnciSYZxYsqSQpVWypJMlnSwrZJkeihSyvLYxYkEUyLK+MWYi4ckgh8e6GVmmFpHJMlx3scuysjFmhSyvkmWdLOtkRSEr1AEKGRTBk5FAVmiD4MgKzVWWDGKXRLIidRkE+AAvkhXaIViyQk/aS7KikJVVsqKTFZXMG5nMG1ocZLLnIvnfDyxwAtlzKpF5o3cZ5tCkUzIMvErmDe0QHBnG1nBk3shk3qyRee7vjRcybySyu38=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Column1 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Column1", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Holiday"}})
in
#"Renamed Columns"

WorkSchedule querry: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8s3PS0msVNJRMtczVYrViVYKKU0tRhUJT03JQxcLySgtQhNyK8qECijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Day = _t, Hours = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Day", type text}, {"Hours", type number}})
in
#"Changed Type"


I just need help to figure out how to count the weekends (count both saturday and sunday individually, so for every relevant weekend the count  shoul be 2) in which the employee was sick Friday before and Monday after that weekend. For public holidays I need a measure that will count holiday in which employee was sick day before AND after  the holiday. And then I need a measure that will add those counts to the count of the column IsFullTimeSick.

Thank you for the help 

[Mateja]

Date	Emp	Hours
2022-04-01	25	0
2022-04-04	25	0
2022-04-05	25	0
2022-04-06	25	0
2022-04-07	25	0
2022-04-08	25	0
2022-04-11	25	0
2022-04-12	25	0
2022-04-13	25	0
2022-04-19	25	0
2022-04-20	25	0
2022-04-21	25	0
2022-04-22	25	0
2022-04-23	25	0
2022-04-24	25	0
2022-04-25	25	0
2022-04-26	25	0
2022-04-27	25	0
2022-04-28	25	0
2022-04-29	25	0
2022-04-30	25	0
2022-05-01	25	0
2022-05-02	25	0
2022-05-03	25	0
2022-05-04	25	0
2022-05-06	25	0
2022-05-09	25	0
2022-05-10	25	0
2022-05-23	25	0
2022-05-24	25	0
2022-05-25	25	0
2022-05-26	25	0
2022-05-27	25	0
2022-05-28	25	0
2022-05-29	25	0
2022-05-30	25	0
2022-05-31	25	0
2022-06-01	25	0
2022-06-02	25	0
2022-06-03	25	0
2022-06-04	25	0
2022-06-05	25	0
2022-06-06	25	0
2022-06-07	25	0
2022-06-08	25	0
2022-06-09	25	0
2022-06-10	25	0
2022-06-11	25	0
2022-06-12	25	0
2022-06-13	25	0
2022-06-14	25	0
2022-07-06	25	0
2022-07-07	25	0
2022-07-08	25	0
2022-07-11	25	0
2022-07-12	25	0
2022-07-13	25	0
2022-07-14	25	0
2022-07-15	25	0
2022-07-16	25	0
2022-07-17	25	0
2022-07-18	25	0
2022-07-19	25	0
2022-07-20	25	0
2022-07-21	25	0
2022-07-22	25	7
2022-07-25	25	7.5
2022-07-26	25	7.5
2022-07-27	25	7.5
2022-07-28	25	7.5
2022-07-29	25	7
2022-08-08	25	7.5
2022-08-09	25	7.5
2022-08-10	25	7.5
2022-08-11	25	7.5
2022-08-12	25	7
2022-08-15	25	7.5
2022-08-16	25	7.5
2022-08-17	25	7.5
2022-08-18	25	7.5
2022-08-19	25	7
2022-08-22	25	7.5
2022-08-23	25	7.5
2022-08-24	25	7.5
2022-08-25	25	7.5
2022-08-26	25	7
2022-08-29	25	7.5
2022-08-30	25	7.5
2022-08-31	25	7.5
2022-09-01	25	7.5
2022-09-02	25	7
2022-09-05	25	7.5
2022-09-06	25	7.5
2022-09-07	25	7.5
2022-09-08	25	7.5
2022-09-09	25	7
2022-09-12	25	7.5
2022-09-13	25	7.5
2022-09-14	25	7.5
2022-09-15	25	7.5
2022-09-16	25	7
2022-09-19	25	7.5
2022-09-20	25	7.5
2022-09-21	25	7.5
2022-09-22	25	7.5
2022-09-23	25	7
2022-09-26	25	7.5
2022-09-27	25	7.5
2022-09-28	25	7.5
2022-09-29	25	7.5
2022-09-30	25	7
2022-10-03	25	7.5
2022-10-04	25	7.5
2022-10-05	25	7.5
2022-10-06	25	7.5
2022-10-07	25	7
2022-10-10	25	7.5
2022-10-11	25	7.5
2022-10-12	25	7.5
2022-10-13	25	7.5
2022-10-14	25	7
2022-10-17	25	7.5
2022-10-18	25	7.5
2022-10-19	25	7.5
2022-10-20	25	7.5
2022-10-21	25	7
2022-10-24	25	7.5
2022-10-25	25	7.5
2022-10-26	25	7.5
2022-10-27	25	7.5
2022-10-28	25	7
2022-10-31	25	7.5
2022-11-01	25	7.5
2022-11-02	25	7.5
2022-11-03	25	7.5
2022-11-04	25	7
2022-11-07	25	7.5
2022-11-08	25	7.5
2022-11-09	25	7.5
2022-11-10	25	7.5
2022-11-11	25	7
2022-11-14	25	7.5
2022-11-15	25	7.5
2022-11-16	25	7.5
2022-11-17	25	7.5
2022-11-18	25	7
2022-11-21	25	7.5
2022-11-22	25	7.5
2022-11-23	25	7.5
2022-11-24	25	7.5
2022-11-25	25	7
2022-11-28	25	7.5
2022-11-29	25	7.5
2022-11-30	25	7.5
2022-12-01	25	5
2022-12-02	25	7
2022-12-05	25	7.5
2022-12-06	25	5
2022-12-07	25	7.5
2022-12-08	25	5
2022-12-09	25	7
2022-12-12	25	7.5
2022-12-13	25	4.5
2022-12-14	25	7.5
2022-12-15	25	4.5
2022-12-16	25	4
2022-12-19	25	7.5
2022-12-20	25	4
2022-12-21	25	7.5
2022-12-22	25	4
2022-12-23	25	7
2022-12-27	25	4
2022-12-28	25	7.5
2022-12-29	25	4
2022-12-30	25	7
2023-01-02	25	7.5
2023-01-03	25	5
2023-01-04	25	7.5
2023-01-05	25	5
2023-01-06	25	4.5
2023-01-09	25	7.5
2023-01-10	25	5
2023-01-11	25	7.5
2023-01-12	25	5
2023-01-13	25	4.5
2023-01-16	25	7.5
2023-01-17	25	5
2023-01-18	25	7.5
2023-01-19	25	5
2023-01-20	25	4.5
2023-01-23	25	7.5
2023-01-24	25	5
2023-01-25	25	7.5
2023-01-26	25	5
2023-01-27	25	4.5
2023-01-30	25	7.5
2023-01-31	25	4.5
2023-02-01	25	7.5
2023-02-02	25	4.5
2023-02-03	25	4
2023-02-06	25	3.5
2023-02-07	25	3.5
2023-02-08	25	7.5
2023-02-09	25	3.5
2023-02-10	25	3
2023-02-13	25	3.5
2023-02-14	25	3.5
2023-02-15	25	7.5
2023-02-16	25	3.5
2023-02-17	25	3
2023-02-20	25	3.5
2023-02-21	25	3.5
2023-02-22	25	7.5
2023-02-23	25	3.5
2023-02-24	25	3
2023-02-27	25	5.5
2023-02-28	25	3.5
2023-03-01	25	7.5
2023-03-02	25	3.5
2023-03-03	25	3
2023-03-06	25	3.5
2023-03-07	25	3.5
2023-03-08	25	7.5
2023-03-09	25	3.5
2023-03-10	25	3
2023-03-13	25	3.5
2023-03-14	25	3.5
2023-03-15	25	7.5
2023-03-16	25	3.5
2023-03-17	25	3
2023-03-20	25	3.5
2023-03-21	25	3.5
2023-03-22	25	7.5
2023-03-23	25	3.5
2023-03-24	25	2.5
2023-03-27	25	3.5
2023-03-28	25	3
2023-03-29	25	7.5
2023-03-30	25	3
2023-03-31	25	3
2023-04-03	25	3.5
2023-04-04	25	3.5
2023-04-05	25	7.5
2023-04-11	25	3.5
2023-04-12	25	7.5
2023-04-13	25	3.5
2023-04-14	25	3
2023-04-17	25	3
2023-04-18	25	2.5
2023-04-19	25	3
2023-04-20	25	3.5
2023-04-21	25	7
2023-04-24	25	3.5
2023-04-25	25	3.5
2023-04-26	25	7.5
2023-04-27	25	3.5
2023-04-28	25	3
2023-05-01	25	3.5
2023-05-02	25	2.5
2023-05-03	25	7.5
2023-05-04	25	2.5
2023-05-08	25	3.5
2023-05-09	25	2.5
2023-05-10	25	7.5
2023-05-11	25	2.5
2023-05-12	25	3
2023-05-15	25	2.5
2023-05-16	25	2.5
2023-05-17	25	2.5
2023-05-19	25	7
2023-05-22	25	2.5
2023-05-23	25	2.5
2023-05-24	25	7.5
2023-05-25	25	3.5
2023-05-26	25	3
2023-05-30	25	2.5
2023-05-31	25	7.5
2023-06-01	25	2.5
2023-06-02	25	2
2023-06-06	25	2.5
2023-06-07	25	7.5
2023-06-08	25	2.5
2023-06-09	25	3
2023-06-12	25	3.5
2023-06-13	25	2.5
2023-06-14	25	7.5
2023-06-15	25	2.5
2023-06-16	25	3
2023-06-27	25	2.5
2023-06-28	25	7.5
2023-06-29	25	2.5
2023-06-30	25	2
2023-07-03	25	3
2023-07-04	25	3
2023-07-05	25	7.5
2023-07-06	25	2.5
2023-07-07	25	3
2023-07-10	25	3
2023-07-11	25	3
2023-07-12	25	7.5
2023-07-13	25	2.5
2023-07-14	25	3
2023-07-17	25	3
2023-07-18	25	3
2023-07-19	25	7.5
2023-07-20	25	3
2023-07-21	25	2.5