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 all, i am new to Power BI and this is my first question on here.
I am writing a time and attendance report and i need to do a calculation of the time between clock in and clock out. However, the clock in and clock out are on seperate rows, there could be many users (workers) who have "clocked in" in that time and they could have clocked in and out multiple times per day. So i am stuck and need your help.
I have never posted a question before and i am not too sure how to attach my data. So i have done it as a table below.
JournalRegistrationType = if it is a clock in or clock out
Worker Personnel Number = unique user ID
StartDateTime = the time of the clock in or out.
I would also need to add an additional section to this where if the AbsenceCodetimeAndAttendanceJobID = Dentist then the sum would be zero. There could be a list of 20 or so different variables here.
Thank you in advance for your help, really apprecite it. Hope i have explained it well.
ElectronicTimecardActivityRegistrationEntryNumber | AbsenceCodeTimeAndAttendanceJobId | TimeProfileDate | JournalRegistrationType | TimeAndAttendanceJobId | StartDateTime | WorkerPersonnelNumber |
68719476736 | 21/11/2019 | SignIn | Test-000145 | 21/11/2019 14:36:11 +00:00 | 50 | |
68719478986 | 21/11/2019 | SignOut | Test-000146 | 21/11/2019 14:36:12 +00:00 | 50 | |
68719477486 | 21/11/2019 | SignIn | Test-000145 | 21/11/2019 14:39:45 +00:00 | 25 | |
68719478236 | 21/11/2019 | SignOut | Test-000146 | 21/11/2019 14:39:46 +00:00 | 25 | |
68719478237 | 22/11/2019 | SignIn | Test-000145 | 22/11/2019 12:25:56 +00:00 | 25 | |
68719478987 | 22/11/2019 | SignOut | Test-000146 | 22/11/2019 12:25:57 +00:00 | 25 | |
68719482031 | 25/11/2019 | SignIn | Test-000145 | 25/11/2019 08:01:00 +00:00 | 191 | |
68719483490 | 25/11/2019 | SignIn | Test-000145 | 25/11/2019 08:01:00 +00:00 | 303 | |
68719483491 | 25/11/2019 | SignOut | Test-000146 | 25/11/2019 12:16:00 +00:00 | 303 | |
68719482032 | 25/11/2019 | SignOut | Test-000146 | 25/11/2019 12:16:00 +00:00 | 191 | |
68719483492 | 25/11/2019 | SignIn | Test-000145 | 25/11/2019 12:38:00 +00:00 | 303 | |
68719482034 | 25/11/2019 | SignIn | Test-000145 | 25/11/2019 12:38:00 +00:00 | 191 | |
68719483493 | 25/11/2019 | SignOut | Test-000146 | 25/11/2019 17:21:00 +00:00 | 303 | |
68719482035 | 25/11/2019 | SignOut | Test-000146 | 25/11/2019 17:21:00 +00:00 | 191 | |
68719483494 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 07:50:00 +00:00 | 303 | |
68719482036 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 08:02:00 +00:00 | 191 | |
68719479736 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 10:13:04 +00:00 | 2 | |
68719478989 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 10:57:26 +00:00 | 50 | |
68719478988 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 10:57:52 +00:00 | 25 | |
68719478990 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 11:06:00 +00:00 | 77 | |
68719479738 | 26/11/2019 | SignIn | Test-000145 | 26/11/2019 11:10:44 +00:00 | 103 | |
68719492490 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 12:30:00 +00:00 | 103 | |
68719479745 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 12:30:00 +00:00 | 25 | |
68719479749 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 12:30:00 +00:00 | 77 | |
68719479743 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 16:17:34 +00:00 | 2 | |
68719483495 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 17:12:00 +00:00 | 303 | |
68719482037 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 17:12:00 +00:00 | 191 | |
68719479006 | 26/11/2019 | SignOut | Test-000146 | 26/11/2019 17:30:21 +00:00 | 50 | |
68719483496 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 08:03:00 +00:00 | 303 | |
68719482038 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 08:03:00 +00:00 | 191 | |
68719479744 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 08:38:22 +00:00 | 2 | |
68719479007 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 09:44:15 +00:00 | 50 | |
68719479746 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 09:56:45 +00:00 | 25 | |
68719479750 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 10:08:31 +00:00 | 77 | |
68719480486 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 12:30:00 +00:00 | 25 | |
68719483497 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 12:51:00 +00:00 | 303 | |
68719483498 | 27/11/2019 | SignIn | Test-000145 | 27/11/2019 13:22:00 +00:00 | 303 | |
68719481241 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 16:19:23 +00:00 | 77 | |
68719480489 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 16:24:29 +00:00 | 50 | |
68719481243 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 16:31:52 +00:00 | 2 | |
68719483499 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 16:41:00 +00:00 | 303 | |
68719482039 | 27/11/2019 | SignOut | Test-000146 | 27/11/2019 16:47:00 +00:00 | 191 | |
68719483500 | 28/11/2019 | SignIn | Test-000145 | 28/11/2019 08:01:00 +00:00 | 303 | |
68719482040 | 28/11/2019 | SignIn | Test-000145 | 28/11/2019 08:02:00 +00:00 | 191 | |
68719483501 | 28/11/2019 | SignOut | Test-000146 | 28/11/2019 17:22:00 +00:00 | 303 | |
68719482041 | 28/11/2019 | SignOut | Test-000146 | 28/11/2019 17:42:00 +00:00 | 191 | |
68719483502 | 29/11/2019 | SignIn | Test-000145 | 29/11/2019 07:58:00 +00:00 | 303 | |
68719482042 | 29/11/2019 | SignIn | Test-000145 | 29/11/2019 08:04:00 +00:00 | 191 | |
68719482043 | 29/11/2019 | SignOut | Test-000146 | 29/11/2019 14:16:00 +00:00 | 191 |
Solved! Go to Solution.
Hi @Anonymous ,
We can try to use the following measure to meet your requirement:
TotolMinutes =
SUMX (
SUMMARIZE (
'Table',
'Table'[WorkerPersonnelNumber],
"Total Time",
SUMX (
'Table',
IF (
[JournalRegistrationType] = "SignOut",
VAR ti = [StartDateTime]
RETURN
DATEDIFF (
CALCULATE (
MAX ( 'Table'[StartDateTime] ),
FILTER (
'Table',
'Table'[JournalRegistrationType] = "SignIn"
&& 'Table'[StartDateTime] < ti
)
),
ti,
MINUTE
),
BLANK ()
)
)
),
[Total Time]
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.
Best regards,
Hi @Anonymous ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @v-lid-msft,
Apologies for the delay, i have been out for a while.
I managed to come up with a solution which works for multiple areas, however, based on a new addition there is now a variable that has me stuck.
Really appreciate your help, that has helped on other reports i am writing.
Thank you,
Steven
Hi @Anonymous ,
We can try to use the following measure to meet your requirement:
TotolMinutes =
SUMX (
SUMMARIZE (
'Table',
'Table'[WorkerPersonnelNumber],
"Total Time",
SUMX (
'Table',
IF (
[JournalRegistrationType] = "SignOut",
VAR ti = [StartDateTime]
RETURN
DATEDIFF (
CALCULATE (
MAX ( 'Table'[StartDateTime] ),
FILTER (
'Table',
'Table'[JournalRegistrationType] = "SignIn"
&& 'Table'[StartDateTime] < ti
)
),
ti,
MINUTE
),
BLANK ()
)
)
),
[Total Time]
)
If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared.
Best regards,
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |