Forum Discussion
Helper ICalculation for consecutive work days for employee
Hello everyone,
I've been tasked with creating a report that tracks consecutive workdays. Within the employee transaction table, there are two key columns: [Employee ID] and [Work Date]. I've already added a new column that's working well, except it's not applied correctly when employees are grouped together.
In an attempt to refine the report further, I introduced another column. Unfortunately, this didn't yield the desired results.
I'm in need of your assistance to make this report accurate and effective. Your help would be greatly appreciated!
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDcAgDETRXVwjnTmDSWZB7L8GRarI177i/70tmLmsmQd6B8NO+yGnwqXwUfgKDFdY7wNemwNkwQlPgfxG5wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"Work Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"Work Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Consecutive", each if [Index]=0 then 1 else List.Accumulate({1..[Index]},1,(state,current)=> if #"Added Index"[Work Date]{current-1}=#"Added Index"[Work Date]{current}+#duration(-1,0,0,0) then state + 1 else 1)) in #"Added Custom"
8 Replies
- lbendlin
Super User
This can be done much simpler in Power Query.
Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
babyjb1979Employee Number Work Date 32667 03/11/23 32667 03/25/23 32667 03/27/23 32667 03/28/23 32667 03/29/23 32667 03/30/23 32667 03/31/23 32667 04/08/23 32667 04/22/23 32667 05/06/23 32667 05/20/23 - babyjb1979
Please revise employee number to different numbers. Thank you!
- babyjb1979
Please revise employee number to different numbers. Thank you!
- babyjb1979
Please advise the solution by Power Query. I am in a hurry! Thank you a million!!!!
- lbendlin
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc2xDcAgDETRXVwjnTmDSWZB7L8GRarI177i/70tmLmsmQd6B8NO+yGnwqXwUfgKDFdY7wNemwNkwQlPgfxG5wI=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Employee Number" = _t, #"Work Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee Number", Int64.Type}, {"Work Date", type date}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Consecutive", each if [Index]=0 then 1 else List.Accumulate({1..[Index]},1,(state,current)=> if #"Added Index"[Work Date]{current-1}=#"Added Index"[Work Date]{current}+#duration(-1,0,0,0) then state + 1 else 1)) in #"Added Custom"