How to create Start Date and End Date columns from one Date Column in a new tabel

[htsvhwave]

I need to make the start date and end date columns from the date column, in a new tabel. I have a table of employees and the dates on which they are absent because of sickness but I need to reduce the table so that there is no separate row for each date if that date is part of a absent period that lasts more than one day.  furthermore i need it to also make a column where is sums up the hours they were absent in that period.

Example Input: 

Name    Date        Type of absence   Hours
Ivana   15/06/2023  Sick              8
Ivana   16/06/2023  Sick              8
Ivana   17/06/2023  Sick              4
Ivana   18/06/2023  Sick              8
Ivana   19/06/2023  Sick              8
Ivana   20/06/2023  Sick              8
Ivana   05/01/2023  Sick - Doctor     2
Ivana   20/05/2023   Sick             8
Ivana   21/05/2023   Sick             5

 Expected Output:

Name    Start Date  EndDate     Hours
Ivana   15/06/2023  20/06/2023   36
Ivana   05/01/2023  05/01/2023   2
Ivana   20/05/2023  21/05/2023   13

[ryan_mayu]

@htsvhwave 

why the first row is not 44?

pls see the attachment below

[htsvhwave]

Hello, i tried your approach however it splits up the period because of the weekend, how can i fix this?

[htsvhwave]

To be more specifik, i want the weekend included in the period. Lets say:

Name    Date        Type of absence   Hours
Ivana   15/06/2023  Sick              8
Ivana   16/06/2023  Sick              8
Ivana   19/06/2023  Sick              8
Ivana   20/06/2023  Sick              8

I want to see this

Name    Start Date  EndDate     Hours
Ivana   15/06/2023  20/06/2023   32

[htsvhwave]

@ryan_mayu 

[ryan_mayu · Accepted solution]

@htsvhwave 

pls see the attahment below

[htsvhwave]

Thank you so much, it seem to work for weekend however how can i also make it consider public holidays in the dax code. I have a date table where i distiguise between weekdays, weekends and holidays

[ryan_mayu]

then you can combine calendar table and holiday table ,then to update the result

[htsvhwave]

Hello @ryan_mayu , i have made a sample file, would you be able to help in how i can considere the holidays in the dax formula

https://drive.google.com/file/d/1255SoUAgTZfRbtww6K3h2uSe3QRPBm65/view?usp=drive_link

[ryan_mayu]

@htsvhwave 

I can't open the google drive, i updated your sample file, pls see the attachment below

[htsvhwave]

i have a seperate data table currently where i have the holidays