Help with calculating time ???

rf27 · ‎01-03-2020

I'm very new to this.. I tried a couple of things...

Jimmy801 · ‎01-04-2020

Hello @rf27

What should the expected output be?

These are time, and having total of times doesn't seem to be of any sense to me.

To me this seems more a topic for power query rather then DAX

All the best

Jimmy

rf27 · ‎01-06-2020

Hi Jimmy,

Thank you for your reply.

What I'm looking for is two things,

Total hours worked per date

Then Total hours per days worked .

Ex: day one- 6 hrs, day 2- 8hrs, day 3- 10hrs, and day 4- 8hrs

Total 32 hours

Jimmy801 · ‎01-06-2020

Hello @rf27

how the start and how the end time is defined? A was not able to identify it. What I did already mention is that in one day i saw 3 times. So how to identify what is the start and what is the end date?

What would also be important to now, is what exactly is you expected output. I can think of transforming date (poviting start and end) and with this a easy measure in DAX could be created.

Jimmy

rf27 · ‎01-06-2020

Hey Jimmy,

The first transaction , and the last transaction of the day counts for the time duration... I cut out the important piece.. I have an 1 and 0 the represents my first in and last transaction. 1 = IN 0= OUT I'm looking to tabulate the IN & OUT of every day... I hope this brings clarity to my questions..

Jimmy801 · ‎01-06-2020

Hello @rf27

are they connected in any way? meaning.. how to belong the first 1 with the first 0?

Possible logics to create your solution could be like this:

- split always every second row

- use the date (if the table exactly contains 2 rows per otherwise - otherwise logic would fail)

Another remark.. so you are doing already a trasforming of your data, keeping only first and last time of a day?

Could you please post a real table, that I can use it to build your solution

Jimmy

rf27 · ‎01-06-2020

Hey Jimmy,

Yes, I have two unique identifiers.

empid, cardnum this connects me to the 1&0 transactions per person.. I also have a filter to get the 1&0 based on the reader desc.

Let me clean up the data set it's rather large

Richard

rf27 · ‎01-06-2020

events.event_time_utc	event.evdescr	reader.readerdesc	empid	events.cardnum	buenos_aires	DATE	TIME	badgetyp.r_name	in_out
12/3/19 8:21 PM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	24808	20600609	12/3/19 5:21 PM	12/03/19	17:21:42	Contractor	0
12/3/19 10:38 PM	Access Granted	1493 ROMULUS T/S 7 EAST  OUT -C23-P2-R2	26792	20302725	12/3/19 7:38 PM	12/03/19	19:38:52	Contractor	0
12/3/19 1:07 PM	Access Granted	1493 ROMULUS T/S 6 EAST  IN -C23-P1-R7	26792	20302725	12/3/19 10:07 AM	12/03/19	10:07:08	Contractor	1
12/3/19 8:28 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	27882	20632002	12/3/19 5:28 PM	12/03/19	17:28:14	Contractor	0
12/3/19 12:28 PM	Access Granted	1443ASM POST 2 TS3 EXIT C03-P1-R1	27983	20632009	12/3/19 9:28 AM	12/03/19	9:28:23	Contractor	0
12/3/19 12:28 PM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	28038	315970	12/3/19 9:28 AM	12/03/19	9:28:20	Contractor	0
12/3/19 8:29 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28064	314473	12/3/19 5:29 PM	12/03/19	17:29:19	Contractor	0
12/3/19 8:33 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28130	520737	12/3/19 5:33 PM	12/03/19	17:33:20	Contractor	0
12/3/19 7:29 PM	Access Granted	1493 ROMULUS T/S 6 EAST  OUT -C23-P1-R8	28188	20302713	12/3/19 4:29 PM	12/03/19	16:29:36	Contractor	0
12/3/19 10:51 AM	Access Granted	1493 ROMULUS T/S 6 EAST  IN -C23-P1-R7	28188	20302713	12/3/19 7:51 AM	12/03/19	7:51:46	Contractor	1
12/3/19 11:56 AM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	28188	20302713	12/3/19 8:56 AM	12/03/19	8:56:41	Contractor	0
12/3/19 2:30 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	28188	20302713	12/3/19 11:30 AM	12/03/19	11:30:54	Contractor	0
12/3/19 2:31 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	28188	20302713	12/3/19 11:31 AM	12/03/19	11:31:34	Contractor	1
12/3/19 11:57 AM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	28188	20302713	12/3/19 8:57 AM	12/03/19	8:57:12	Contractor	1
12/3/19 2:06 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	39905	20500896	12/3/19 11:06 AM	12/03/19	11:06:45	Contractor	0
12/3/19 9:07 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	39905	20500896	12/3/19 6:07 PM	12/03/19	18:07:22	Contractor	0
12/3/19 10:04 PM	Access Granted	1443ASM POST 1 TS3 EXIT C11-P3-R2	40386	20322132	12/3/19 7:04 PM	12/03/19	19:04:22	Unknown	0
12/3/19 6:39 PM	Access Granted	5014 ENT CSS CORREDIZA 3 C14-P6-R1	41691	301497	12/3/19 3:39 PM	12/03/19	15:39:40	Contractor	1
12/3/19 8:10 PM	Access Granted	5014 ENT CSS TOR 4 RA C12-P3-R2	41691	301497	12/3/19 5:10 PM	12/03/19	17:10:54	Contractor	1
12/3/19 1:17 PM	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	12/3/19 10:17 AM	12/03/19	10:17:22	Contractor	1
12/3/19 6:38 PM	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	12/3/19 3:38 PM	12/03/19	15:38:38	Contractor	1
12/3/19 1:16 PM	Access Granted	5014 ENT CSS TOR 1 RA C12-P0-R2	41691	301497	12/3/19 10:16 AM	12/03/19	10:16:42	Contractor	1
12/3/19 8:23 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	41804	20619953	12/3/19 5:23 PM	12/03/19	17:23:22	Contractor	0
12/3/19 3:19 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:19 PM	12/03/19	12:19:40	Contractor	0
12/3/19 5:47 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 2:47 PM	12/03/19	14:47:27	Contractor	0
12/3/19 6:22 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:22 PM	12/03/19	15:22:12	Contractor	0
12/3/19 6:50 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:50 PM	12/03/19	15:50:59	Contractor	0
12/3/19 6:51 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:51 PM	12/03/19	15:51:13	Contractor	0
12/3/19 2:46 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 11:46 AM	12/03/19	11:46:26	Contractor	0
12/3/19 3:14 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:14 PM	12/03/19	12:14:04	Contractor	0
12/3/19 3:25 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:25 PM	12/03/19	12:25:06	Contractor	0
12/3/19 3:34 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:34 PM	12/03/19	12:34:19	Contractor	0
12/3/19 3:37 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:37 PM	12/03/19	12:37:40	Contractor	0
12/3/19 3:45 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:45 PM	12/03/19	12:45:31	Contractor	0
12/3/19 3:47 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:47 PM	12/03/19	12:47:14	Contractor	0
12/3/19 3:56 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 12:56 PM	12/03/19	12:56:52	Contractor	0
12/3/19 4:00 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 1:00 PM	12/03/19	13:00:00	Contractor	0
12/3/19 4:13 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 1:13 PM	12/03/19	13:13:27	Contractor	0
12/3/19 4:44 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 1:44 PM	12/03/19	13:44:50	Contractor	0
12/3/19 5:42 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 2:42 PM	12/03/19	14:42:50	Contractor	0
12/3/19 6:03 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:03 PM	12/03/19	15:03:18	Contractor	0
12/3/19 6:38 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:38 PM	12/03/19	15:38:19	Contractor	0
12/3/19 6:50 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/3/19 3:50 PM	12/03/19	15:50:34	Contractor	0
12/3/19 10:53 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	41838	1116381	12/3/19 7:53 PM	12/03/19	19:53:19	Contractor	0
12/3/19 8:18 AM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	42601	320316	12/3/19 5:18 AM	12/03/19	5:18:12	Contractor	0
12/3/19 11:08 AM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	42601	320316	12/3/19 8:08 AM	12/03/19	8:08:48	Contractor	0
12/3/19 4:07 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	44085	410614	12/3/19 1:07 PM	12/03/19	13:07:15	Contractor	0
12/3/19 10:48 AM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	44085	410614	12/3/19 7:48 AM	12/03/19	7:48:42	Contractor	1
12/4/19 8:25 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	24808	20600609	12/4/19 5:25 PM	12/04/19	17:25:29	Contractor	0
12/4/19 10:23 PM	Access Granted	1493 ROMULUS T/S 7 EAST  OUT -C23-P2-R2	26792	20302725	12/4/19 7:23 PM	12/04/19	19:23:54	Contractor	0
12/4/19 1:16 PM	Access Granted	1493 ROMULUS T/S 6 EAST  IN -C23-P1-R7	26792	20302725	12/4/19 10:16 AM	12/04/19	10:16:18	Contractor	1
12/4/19 8:28 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	27882	20632002	12/4/19 5:28 PM	12/04/19	17:28:04	Contractor	0
12/4/19 4:33 AM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	27915	20616794	12/4/19 1:33 AM	12/04/19	1:33:38	Contractor	0
12/4/19 12:28 PM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	27983	20632009	12/4/19 9:28 AM	12/04/19	9:28:52	Contractor	0
12/4/19 12:28 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28038	315970	12/4/19 9:28 AM	12/04/19	9:28:52	Contractor	0
12/4/19 8:30 PM	Access Granted	1443ASM POST 2 TS3 EXIT C03-P1-R1	28064	314473	12/4/19 5:30 PM	12/04/19	17:30:01	Contractor	0
12/4/19 3:02 PM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	28064	314473	12/4/19 12:02 PM	12/04/19	12:02:32	Contractor	0
12/4/19 8:33 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28130	520737	12/4/19 5:33 PM	12/04/19	17:33:02	Contractor	0
12/4/19 7:29 PM	Access Granted	1493 ROMULUS T/S 6 EAST  OUT -C23-P1-R8	28188	20302713	12/4/19 4:29 PM	12/04/19	16:29:46	Contractor	0
12/4/19 4:40 PM	Access Granted	1493 ROMULUS T/S 6 EAST  OUT -C23-P1-R8	28188	20302713	12/4/19 1:40 PM	12/04/19	13:40:04	Contractor	0
12/4/19 10:55 AM	Access Granted	1493 ROMULUS T/S 5 EAST  IN -C23-P1-R5	28188	20302713	12/4/19 7:55 AM	12/04/19	7:55:23	Contractor	1
12/4/19 4:52 PM	Access Granted	1493 ROMULUS T/S 6 EAST  IN -C23-P1-R7	28188	20302713	12/4/19 1:52 PM	12/04/19	13:52:17	Contractor	1
12/4/19 2:08 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	28188	20302713	12/4/19 11:08 AM	12/04/19	11:08:34	Contractor	0
12/4/19 2:09 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	28188	20302713	12/4/19 11:09 AM	12/04/19	11:09:21	Contractor	1
12/4/19 10:02 PM	Access Granted	1443ASM POST 1 TS3 EXIT C11-P3-R2	40386	20322132	12/4/19 7:02 PM	12/04/19	19:02:21	Unknown	0
12/4/19 7:29 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	41804	20619953	12/4/19 4:29 PM	12/04/19	16:29:07	Contractor	0
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 1 EXIT C21-P0-R1	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:32	Contractor	0
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 2 EXIT C21-P1-R2	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:35	Contractor	0
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 2 EXIT C22-P1-R2	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:22	Contractor	0
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 3 EXIT C22-P2-R2	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:19	Contractor	0
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 3 EXIT C21-P2-R2	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:38	Contractor	0
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 1 EXIT C22-P0-R2	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:27	Contractor	0
12/4/19 1:28 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/4/19 10:28 AM	12/04/19	10:28:50	Contractor	0
12/4/19 1:24 PM	Access Granted	1443ASM PERSONNEL ADMIN EXT DOOR EXIT C15-P1-R1	41838	1116381	12/4/19 10:24 AM	12/04/19	10:24:23	Contractor	0
12/4/19 3:15 PM	Access Granted	1443ASM BODY SHOP T/S 2 EXIT C20-P2-R2	41838	1116381	12/4/19 12:15 PM	12/04/19	12:15:32	Contractor	0
12/4/19 3:11 PM	Access Granted	1443ASM POST 1 TS2 EXIT C01-P0-R2	41838	1116381	12/4/19 12:11 PM	12/04/19	12:11:40	Contractor	0
12/4/19 3:11 PM	Access Granted	1443ASM POST 1 TS1 EXIT C01-P0-R1	41838	1116381	12/4/19 12:11 PM	12/04/19	12:11:41	Contractor	0
12/4/19 3:15 PM	Access Granted	1443ASM BODY SHOP T/S 1 EXIT C20-P1-R2	41838	1116381	12/4/19 12:15 PM	12/04/19	12:15:35	Contractor	0
12/4/19 3:30 PM	Access Granted	1443ASM PAINT SHOP TS 2 EXIT C18-P2-R1	41838	1116381	12/4/19 12:30 PM	12/04/19	12:30:03	Contractor	0
12/4/19 3:30 PM	Access Granted	1443ASM PAINT SHOP TS 1 EXIT C18-P1-R2	41838	1116381	12/4/19 12:30 PM	12/04/19	12:30:01	Contractor	0
12/4/19 3:30 PM	Access Granted	1443ASM PAINT SHOP TS 3 EXIT C18-P3-R1	41838	1116381	12/4/19 12:30 PM	12/04/19	12:30:07	Contractor	0
12/4/19 3:11 PM	Access Granted	1443ASM POST 1 TS3 EXIT C11-P3-R2	41838	1116381	12/4/19 12:11 PM	12/04/19	12:11:36	Contractor	0
12/4/19 7:00 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	41838	1116381	12/4/19 4:00 PM	12/04/19	16:00:05	Contractor	0
12/4/19 3:11 PM	Access Granted	1443ASM POST 1 TS4 EXIT C11-P3-R1	41838	1116381	12/4/19 12:11 PM	12/04/19	12:11:35	Contractor	0
12/4/19 6:38 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	41838	1116381	12/4/19 3:38 PM	12/04/19	15:38:32	Contractor	0
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 1 ENT C22-P0-R1	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:05	Contractor	1
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 3 ENT C22-P2-R1	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:10	Contractor	1
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 3 ENT C21-P2-R1	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:17	Contractor	1
12/4/19 3:03 PM	Access Granted	1443ASM POST 3 TURNSTILE 2 ENT C22-P1-R1	41838	1116381	12/4/19 12:03 PM	12/04/19	12:03:07	Contractor	1
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 2 ENT C21-P1-R1	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:20	Contractor	1
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH TURNSTILE 1 ENT C21-P0-R2	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:23	Contractor	1
12/4/19 3:00 PM	Access Granted	1443ASM TRIM SOUTH ADA ENT C21-P3-R1	41838	1116381	12/4/19 12:00 PM	12/04/19	12:00:26	Contractor	1
12/4/19 11:00 AM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	42601	320316	12/4/19 8:00 AM	12/04/19	8:00:22	Contractor	0
12/4/19 7:31 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	44085	410614	12/4/19 4:31 PM	12/04/19	16:31:35	Contractor	0
12/4/19 10:17 AM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	44085	410614	12/4/19 7:17 AM	12/04/19	7:17:23	Contractor	1
12/5/19 8:22 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	24808	20600609	12/5/19 5:22 PM	12/05/19	17:22:12	Contractor	0
12/5/19 11:35 PM	Access Granted	1493 ROMULUS T/S 6 EAST  OUT -C23-P1-R8	26792	20302725	12/5/19 8:35 PM	12/05/19	20:35:47	Contractor	0
12/5/19 1:58 PM	Access Granted	1493 ROMULUS T/S 6 EAST  IN -C23-P1-R7	26792	20302725	12/5/19 10:58 AM	12/05/19	10:58:03	Contractor	1
12/5/19 8:28 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	27882	20632002	12/5/19 5:28 PM	12/05/19	17:28:05	Contractor	0
12/5/19 4:33 AM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	27915	20616794	12/5/19 1:33 AM	12/05/19	1:33:38	Contractor	0
12/5/19 12:28 PM	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	27983	20632009	12/5/19 9:28 AM	12/05/19	9:28:23	Contractor	0
12/5/19 12:28 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28038	315970	12/5/19 9:28 AM	12/05/19	9:28:28	Contractor	0
12/5/19 8:28 PM	Access Granted	1443ASM POST 2 TS3 EXIT C03-P1-R1	28064	314473	12/5/19 5:28 PM	12/05/19	17:28:45	Contractor	0
12/5/19 8:33 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	28130	520737	12/5/19 5:33 PM	12/05/19	17:33:33	Contractor	0
12/5/19 10:54 AM	Access Granted	1493 ROMULUS T/S 7 EAST  IN -C23-P2-R1	28188	20302713	12/5/19 7:54 AM	12/05/19	7:54:49	Contractor	1
12/5/19 7:29 PM	Access Granted	1493 ROMULUS T/S 5 EAST  OUT -C23-P1-R6	28188	20302713	12/5/19 4:29 PM	12/05/19	16:29:28	Contractor	0
12/5/19 2:14 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	28188	20302713	12/5/19 11:14 AM	12/05/19	11:14:30	Contractor	0
12/5/19 2:39 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY OUT -C22-P2-R2	28188	20302713	12/5/19 11:39 AM	12/05/19	11:39:01	Contractor	0
12/5/19 2:14 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	28188	20302713	12/5/19 11:14 AM	12/05/19	11:14:59	Contractor	1
12/5/19 2:39 PM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	28188	20302713	12/5/19 11:39 AM	12/05/19	11:39:25	Contractor	1
12/5/19 1:09 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	39905	20500896	12/5/19 10:09 AM	12/05/19	10:09:13	Contractor	0
12/5/19 8:53 PM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	39905	20500896	12/5/19 5:53 PM	12/05/19	17:53:03	Contractor	0
12/5/19 10:03 PM	Access Granted	1443ASM POST 1 TS4 EXIT C11-P3-R1	40386	20322132	12/5/19 7:03 PM	12/05/19	19:03:06	Unknown	0
12/5/19 7:14 PM	Access Granted	5014 ENT CSS TOR 3 RA C12-P2-R2	41691	301497	12/5/19 4:14 PM	12/05/19	16:14:39	Contractor	1
12/5/19 7:08 PM	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	41804	20619953	12/5/19 4:08 PM	12/05/19	16:08:23	Contractor	0
12/5/19 11:10 AM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	42601	320316	12/5/19 8:10 AM	12/05/19	8:10:09	Contractor	0
12/5/19 11:13 AM	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	42601	320316	12/5/19 8:13 AM	12/05/19	8:13:19	Contractor	0
12/5/19 7:25 PM	Access Granted	1493 ROMULUS T/S 5 EAST  OUT -C23-P1-R6	44085	410614	12/5/19 4:25 PM	12/05/19	16:25:36	Contractor	0
12/5/19 10:50 AM	Access Granted	1493 ROMULUS T/S SECURITY LOBBY IN -C22-P2-R1	44085	410614	12/5/19 7:50 AM	12/05/19	7:50:27	Contractor	1

Jimmy801 · ‎01-06-2020

Hello @rf27

i tried to analyse your data withou success. Grouped by the two columns i got this examples (only with nulls)

03.12.2019 20:21:00	Access Granted	1443ASM POST 2 TS4 EXIT C03-P3-R1	24808	20600609	03.12.2019 17:21:00	03.12.2019	17:21:42	Contractor
04.12.2019 20:25:00	Access Granted	1443ASM POST 2 TS5 EXIT C03-P2-R1	24808	20600609	04.12.2019 17:25:00	04.12.2019	17:25:29	Contractor
05.12.2019 20:22:00	Access Granted	1443ASM POST 2 TS6 EXIT C03-P2-R2	24808	20600609	05.12.2019 17:22:00	05.12.2019	17:22:12	Contractor

for others only 1

03.12.2019 18:39:00	Access Granted	5014 ENT CSS CORREDIZA 3 C14-P6-R1	41691	301497	03.12.2019 15:39:00	03.12.2019	15:39:40	Contractor	1
03.12.2019 20:10:00	Access Granted	5014 ENT CSS TOR 4 RA C12-P3-R2	41691	301497	03.12.2019 17:10:00	03.12.2019	17:10:54	Contractor	1
03.12.2019 13:17:00	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	03.12.2019 10:17:00	03.12.2019	10:17:22	Contractor	1
03.12.2019 18:38:00	Access Granted	5014 ENT CSS CORREDIZA 2 C14-P7-R1	41691	301497	03.12.2019 15:38:00	03.12.2019	15:38:38	Contractor	1
03.12.2019 13:16:00	Access Granted	5014 ENT CSS TOR 1 RA C12-P0-R2	41691	301497	03.12.2019 10:16:00	03.12.2019	10:16:42	Contractor	1
05.12.2019 19:14:00	Access Granted	5014 ENT CSS TOR 3 RA C12-P2-R2	41691	301497	05.12.2019 16:14:00	05.12.2019	16:14:39	Contractor	1

Jimmy

rf27 · ‎01-06-2020

How did you use the Group by ? What Columns ?

Jimmy801 · ‎01-06-2020

Hello @rf27

I grouped by the columns stated by you "empid" and "cardnum"

Jimmy

kentyler · ‎01-03-2020

DAX does not have a built in concept like "previous row".

To compare a time in one row to a time in the previous row you need to do 3 things

Create an index for the table in power query, using "add column"

Store the index for the current row in a variable

Use lookupvalue() to get the value you want from the previous row by looking for the index -1

In your case you would also have to check that the day in the previous row was the same day. If it was you would have the 2 datetime values you needed to use DATEDIFF() since it can compare by minute if you like.

If you need help with the DAX code, paste in a copy of your data (rather than using an image) and I'll copy and paste it into a power bi file and write some sample code for you.

I'm a personal Power Bi Trainer I learn something every time I answer a question

The Golden Rules for Power BI

Use a Calendar table. A custom Date tables is preferable to using the automatic date/time handling capabilities of Power BI. https://www.youtube.com/watch?v=FxiAYGbCfAQ
Build your data model as a Star Schema. Creating a star schema in Power BI is the best practice to improve performance and more importantly, to ensure accurate results! https://www.youtube.com/watch?v=1Kilya6aUQw
Use a small set up sample data when developing. When building your measures and calculated columns always use a small amount of sample data so that it will be easier to confirm that you are getting the right numbers.
Store all your intermediate calculations in VARs when you’re writing measures. You can return the VARs to check on your steps along the way.

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

rf27 · ‎01-09-2020

Hi Ken,

Is it possible you could send me an example ? I have created the index, but I'm still having issues with this..

Any help is greatly appreciated ....

Richard

kentyler · ‎01-09-2020

How about doing a screen share ? Its easier to look into complex problems that way. Just email me a good date and time and I'll send you a meeting invitation. ken@8thfold.com

Did this post answer your question? Mark it as a solution so others can find it!

Help when you know. Ask when you don't!

Join the conversation at We Talk BI find out more about me at Slow BI

rf27 · ‎01-06-2020

Hi Ken,

Thanks for your reply... I'll give it a go..

Richard

Help with calculating time ???

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025

Party with Power BI’s own Guy in a Cube

Help with calculating time ???

Helpful resources

Fabric Community Update - July 2025

Power BI Monthly Update - July 2025