Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
rf27
Regular Visitor

Help with calculating time ???

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

 
 

2020-01-03_14-57-05.png

 

14 REPLIES 14
Jimmy801
Community Champion
Community Champion

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

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
Community Champion
Community Champion

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

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..  

2020-01-06_10-18-28.png

Jimmy801
Community Champion
Community Champion

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

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
Regular Visitor

 

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
Community Champion
Community Champion

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:00Access Granted1443ASM POST 2 TS4 EXIT C03-P3-R1248082060060903.12.2019 17:21:0003.12.201917:21:42Contractor0
04.12.2019 20:25:00Access Granted1443ASM POST 2 TS5 EXIT C03-P2-R1248082060060904.12.2019 17:25:0004.12.201917:25:29Contractor0
05.12.2019 20:22:00Access Granted1443ASM POST 2 TS6 EXIT C03-P2-R2248082060060905.12.2019 17:22:0005.12.201917:22:12Contractor0

 

for others only 1

 

03.12.2019 18:39:00Access Granted5014 ENT CSS CORREDIZA 3 C14-P6-R14169130149703.12.2019 15:39:0003.12.201915:39:40Contractor1
03.12.2019 20:10:00Access Granted5014 ENT CSS TOR 4 RA C12-P3-R24169130149703.12.2019 17:10:0003.12.201917:10:54Contractor1
03.12.2019 13:17:00Access Granted5014 ENT CSS CORREDIZA 2 C14-P7-R14169130149703.12.2019 10:17:0003.12.201910:17:22Contractor1
03.12.2019 18:38:00Access Granted5014 ENT CSS CORREDIZA 2 C14-P7-R14169130149703.12.2019 15:38:0003.12.201915:38:38Contractor1
03.12.2019 13:16:00Access Granted5014 ENT CSS TOR 1 RA C12-P0-R24169130149703.12.2019 10:16:0003.12.201910:16:42Contractor1
05.12.2019 19:14:00Access Granted5014 ENT CSS TOR 3 RA C12-P2-R24169130149705.12.2019 16:14:0005.12.201916:14:39Contractor1

 

Jimmy

How did you use the Group by ?   What Columns ?

 

Jimmy801
Community Champion
Community Champion

Hello @rf27 

 

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

 

Jimmy

kentyler
Solution Sage
Solution Sage

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

  1. 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
  2. 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
  3. 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.
  4. 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


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

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


Hi Ken,

 

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

 

Richard

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.