Absence period calculation

[htsvhwave]

Hi

I need help to calculate absence period of a group of employees as they have as they working days are much differnt than the regular employee, because the can work during public holidays and weekends and have time of during the week day. I have a table where all their hours are registerede and need to create a new table for absence period of the employees. An example of the table i have can be seen below and the resulted outcome that i would like.

Name    Date        Type of absence  Hours 
Adam   01/04/2023    Sick              8
Adam   02/04/2023    Sick              8
Adam   04/04/2023    Sick              8
Adam   05/04/2023    Sick              8
Adam   06/04/2023    Sick              8
Adam   07/04/2023    Sick              8
Adam   12/04/2023    Sick              8
Adam   13/04/2023    Sick              8
Kyle   08/03/2023    Work              8
Kyle   09/03/2023    Work              8 
Kyle   10/03/2023    Sick              8 
Kyle   12/03/2023    Sick              6 
Kyle   14/03/2023    Sick              8 
Kyle   15/03/2023    Sick              8
Kyle   16/03/2023    Work              8 

 Public holiday tabel

Name   Date     
xx     06/04/2023  
yy     07/04/2023  
zz     9/04/2023  
QQ     10/04/2023

 Expected Output:

Name    Start Date  EndDate     Hours
Adam    01/04/2023  13/04/2023   64
Kyle    10/03/2023  15/03/2023   30

[vicky_]

Based on your description, i've written the measure 

Absent Hours = CALCULATE(SUM('Table'[Hours]), 'Table'[Type] <> "Work")

it seems like you just need to sum the hours that aren't of type work (<> is DAX for not equals). And you can get the start and end time using earliest / latest (there's no need to write a measure specifically, unless the requirements change)

[htsvhwave]

However every employees has multiple absence periods

[v-yueyunzh-msft · Accepted solution]

Hi ,  @htsvhwave 

According to your descripotion,  employees has multiple absence periods. My understand for your need is like this :

If this , i think we can realize it in Power Query Editor.

You can create a blank query in Power Query Editor:

Then we can put this M code in the "Advanced Editor":

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WckxJzFXSUTLRN9Q3MjAyBjKDM5OzgZSFUqwOkrQRfmlj/NIm+KVNYdLh+UVYpM3w6zbHL21IwOmG2N3uXZmTCuQY61tgdRxc2hK/tKEBftNxuA4hjz3oEPKmBOTNCLgPe+gh5C0IyBPwvxEW/8cCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, #"Type of absence" = _t, Hours = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Date", type date}, {"Type of absence", type text}, {"Hours", Int64.Type}}),
#"Sorted Rows" = Table.Sort(#"Changed Type",{{"Name", Order.Ascending}, {"Date", Order.Ascending}}),
#"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each
if [Type of absence] = "Work" then 0 else
if Table.IsEmpty(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and y[Type of absence] = "Work" )) then 1
else List.Max(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and y[Type of absence] = "Work" )[Index])

),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> 0)),
Custom1 = Table.Group(#"Filtered Rows",{"Name","Custom"},
{{"Start Date",(x)=>List.Min(x[Date])} , {"End Date",(x)=>List.Max(x[Date])} , {"Hours",(x)=> List.Sum(x[Hours]) } }

)
in
Custom1

You can refer to the corresponding steps of my test data, and then try to see if it can meet your needs on your data.

 

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[htsvhwave]

Im having some issue regarding this part, if i have more than one type of work absence how do i but it in and also how do you regard the holidays in this?

if [Type of absence] = "Work" then 0 else
if Table.IsEmpty(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and y[Type of absence] = "Work" )) then 1
else List.Max(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and y[Type of absence] = "Work" )[Index])

),

[v-yueyunzh-msft]

Hi , @htsvhwave 

Thanks for your quick response ! Do you mean you have not just "Work" to judge .

If this , you try to use this M code :

= Table.AddColumn(#"Added Index", "Custom", each
if List.ContainsAny({[Type of absence]},{"Work","Work1","Work2"}) then 0 else
if Table.IsEmpty(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and   List.ContainsAny({y[Type of absence]},{"Work","Work1","Work2"})    ))  then 1
else List.Max(Table.SelectRows(#"Added Index",(y)=>y[Index]<[Index] and List.ContainsAny({y[Type of absence]},{"Work","Work1","Work2"})   )[Index]) 

      )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly