calculate employee break time

[romanbull]

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

[Anonymous]

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?

[Phil_Seamark]

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

[romanbull]

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.

[Phil_Seamark]

HI @romanbull

So was my suggested code helpful?  Do you need my help to tweak it more?

[romanbull · Accepted 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.