Forum Discussion
Absence period calculation
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
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
Custom1You can refer to the corresponding steps of my test data, and then try to see if it can meet your needs on your data.
5 Replies
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)
However every employees has multiple absence periods
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
Custom1You can refer to the corresponding steps of my test data, and then try to see if it can meet your needs on your data.
