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.
I have been assigned with task to find break timings availed by each employee using the following door access data by each employee. Door1 is the main entrance, door 3 is production area. E.g. Employee AK entered building through door1 on 6/1/18 at 18:18, then reached door3 at 18:19. AK then went out for a break first via door3 and then via door1 at 18:29 and returned back to production area via door1 at 19:00 and door3 at 19:01, like wise so on.
My requirement is to find the following:
AK Shift Start Time: 6/1/18 at 18:18
First Break: 6/1/18 at 18:29 to 19:01 = 32 min break
Second Break: 6/1/18 at 20:28 to 6/1/18 20:49 = 21 min break
Third Break: 6/1/18 at 20:58 to 6/1/18 21:47 = 49 min break and so on.
Tried using DAX (MINIF and MAXIF) and was able to make out shift start and end time. Need help to find break timings taken by each employee.
Date | Time | Door | Name |
1-Jun | 6/1/2018 18:18 | Door1 | AK |
1-Jun | 6/1/2018 18:19 | Door3 | AK |
1-Jun | 6/1/2018 18:29 | Door3 | AK |
1-Jun | 6/1/2018 18:29 | Door1 | AK |
1-Jun | 6/1/2018 18:51 | Door1 | AJ |
1-Jun | 6/1/2018 18:51 | Door3 | AJ |
1-Jun | 6/1/2018 19:00 | Door1 | AK |
1-Jun | 6/1/2018 19:01 | Door3 | AK |
1-Jun | 6/1/2018 20:28 | Door3 | AK |
1-Jun | 6/1/2018 20:49 | Door3 | AK |
1-Jun | 6/1/2018 20:58 | Door3 | AK |
1-Jun | 6/1/2018 20:59 | Door1 | AK |
1-Jun | 6/1/2018 21:47 | Door1 | AK |
1-Jun | 6/1/2018 21:47 | Door3 | AK |
1-Jun | 6/1/2018 21:53 | Door3 | AJ |
1-Jun | 6/1/2018 21:54 | Door3 | AJ |
1-Jun | 6/1/2018 21:54 | Door2 | AJ |
1-Jun | 6/1/2018 21:55 | Door1 | AJ |
1-Jun | 6/1/2018 22:11 | Door1 | AJ |
1-Jun | 6/1/2018 22:15 | Door1 | AJ |
1-Jun | 6/1/2018 22:16 | Door3 | AJ |
1-Jun | 6/1/2018 23:17 | Door3 | AK |
1-Jun | 6/1/2018 23:42 | Door1 | AK |
1-Jun | 6/1/2018 23:43 | Door3 | AK |
1-Jun | 6/2/2018 0:11 | Door3 | AJ |
1-Jun | 6/2/2018 0:11 | Door2 | AJ |
1-Jun | 6/2/2018 0:14 | Door3 | AJ |
1-Jun | 6/2/2018 1:45 | Door3 | AJ |
1-Jun | 6/2/2018 1:45 | Door2 | AJ |
1-Jun | 6/2/2018 1:46 | Door2 | AJ |
1-Jun | 6/2/2018 1:46 | Door3 | AJ |
1-Jun | 6/2/2018 2:46 | Door3 | AK |
1-Jun | 6/2/2018 2:47 | Door1 | AK |
1-Jun | 6/2/2018 4:06 | Door3 | AJ |
Solved! Go to Solution.
Hi @Phil_Seamark,
Yes need your help in tweaking code. Objective is leave out the first and last door access for shift start and shift end. Then I need to calc time difference between each pair of door3 access as indicated in image where I have manually calculated and then add up those break times.
The solution is not working for me.
Minutes To next Door = VAR PreviousDoorTime = MAXX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && 'Table1'[Time] < EARLIER('Table1'[Time]) ), 'Table1'[Time] ) VAR PreviousDoorNumber = MAXX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && 'Table1'[Time] = PreviousDoorTime), 'Table1'[Door]) VAR x = MINX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && <-- it shows me red line and says (EARLIER/EARLIEST refers to an earlier row context which doesn't exists 'Table1'[Time] > EARLIER('Table1'[Time]) ), 'Table1'[Time]) RETURN SWITCH( TRUE() , PreviousDoorNumber = "Door3" && 'Table1'[Door] = "Door3", DATEDIFF('Table1'[Time],x,MINUTE))
Have you had any solution on this?
Hi @romanbull
Something along these lines might be required. You have a few rows for the same person that share the exact same minute which makes it trickish. With a few small tweaks, it could be what you need. I have attached a PBIX File
Minutes To next Door = VAR PreviousDoorTime = MAXX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && 'Table1'[Time] < EARLIER('Table1'[Time]) ), 'Table1'[Time] ) VAR PreviousDoorNumber = MAXX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && 'Table1'[Time] = PreviousDoorTime), 'Table1'[Door]) VAR x = MINX( FILTER( 'Table1', 'Table1'[Name] = EARLIER(Table1[Name]) && 'Table1'[Time] > EARLIER('Table1'[Time]) ), 'Table1'[Time]) RETURN SWITCH( TRUE() , PreviousDoorNumber = "Door3" && 'Table1'[Door] = "Door3", DATEDIFF('Table1'[Time],x,MINUTE))
Thank you @Phil_Seamark for your assistance, as you said the data is kind of trickish. Data doesn't specify whether the timestamp is an exit or an entry and it only leads to speculation. I worked with your PBIX and is this the correct way what I tried to create from your file?
Manually, I tried to document the breaktime in excel. Not sure how this can be coded in powerbi to get similar kind of result.
Hi @Phil_Seamark,
Yes need your help in tweaking code. Objective is leave out the first and last door access for shift start and shift end. Then I need to calc time difference between each pair of door3 access as indicated in image where I have manually calculated and then add up those break times.
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |