Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
who can help me?
I have a table like this:
ID | EMPLOYEE | |IN/OUT | Date | Time 1 1002 IN 2017-01-01 08:00 2 ...... 5 1002 OUT 2017-01-01 18:00 6 ......
Now i want to know the attendance time for the EMPLOYEE keeping in mind that there a more rows for another people
It also can happen that people works in a nightshift
ID | EMPLOYEE | |IN/OUT | Date | Time 1 1002 IN 2017-01-01 22:00 2 ..... 5 1002 OUT 2017-01-02 07:00 6 .....
Also then i want to know the time
Wiht kind regards,
Norbertus
Solved! Go to Solution.
In Power Query you can:
let Source = Table1, #"Inserted Merged Date and Time" = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime), #"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"EMPLOYEE", Order.Ascending}, {"DateTime", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter), #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"EMPLOYEE", "IN/OUT", "DateTime"}, {"Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}), #"Added Custom" = Table.AddColumn(#"Expanded Prev", "TimeDifference", each if [EMPLOYEE] = [Prev.EMPLOYEE] and [#"Prev.IN/OUT"] = "IN" and [#"IN/OUT"] = "OUT" then [DateTime] - [Prev.DateTime] else null, type nullable duration), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"DateTime", "Index", "Index.1", "Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}) in #"Removed Columns"
Hi @Anonymous,
You can refer to below formula to achieve your requirement.
Table:
Measure:
diff = var current_user=LASTNONBLANK(Record[User],[User]) var current_date=MAX(Record[DateTime]) var current_state=LASTNONBLANK(Record[Actio],[Actio]) var min_date=SWITCH(current_state,"Login",current_date,"Logoff",MAXX(FILTER(ALL(Record),[User]=current_user&&[DateTime]<current_date),[DateTime])) var max_date=SWITCH(current_state,"Login",MINX(FILTER(ALL(Record),[User]=current_user&&[DateTime]>current_date),[DateTime]),"Logoff",current_date) return DATEDIFF(min_date,max_date,MINUTE)
Result:
Notice: datetime are merged by date and time column, 'user' = 'employee', 'action' = 'in/out'.
Regards,
Xiaoxin Sheng
what if we have more than 1 login and log off for the same employee in the same day, so u have any solution for that
help please as for the same employee i have more than login and log off for the same day and I need to calculate networking hours ( latest exit time - earliest entry time ) for same-day and same employee
Hey,
wondering if its possible that there are more ins and outs for one person, or a little rephrased. Your are looking for the duration a a person has been in when leaving (out), whereas this duration is the difference between the in and its immediate following out.
Right?
hi,
There are more Employees so i don't know which row is the OUT time for one specific Employee.
I need to know the time between IN and OUT for one Employee
In Power Query you can:
let Source = Table1, #"Inserted Merged Date and Time" = Table.AddColumn(Source, "DateTime", each [Date] & [Time], type datetime), #"Sorted Rows" = Table.Sort(#"Inserted Merged Date and Time",{{"EMPLOYEE", Order.Ascending}, {"DateTime", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Prev",JoinKind.LeftOuter), #"Expanded Prev" = Table.ExpandTableColumn(#"Merged Queries", "Prev", {"EMPLOYEE", "IN/OUT", "DateTime"}, {"Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}), #"Added Custom" = Table.AddColumn(#"Expanded Prev", "TimeDifference", each if [EMPLOYEE] = [Prev.EMPLOYEE] and [#"Prev.IN/OUT"] = "IN" and [#"IN/OUT"] = "OUT" then [DateTime] - [Prev.DateTime] else null, type nullable duration), #"Sorted Rows1" = Table.Sort(#"Added Custom",{{"ID", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows1",{"DateTime", "Index", "Index.1", "Prev.EMPLOYEE", "Prev.IN/OUT", "Prev.DateTime"}) in #"Removed Columns"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |