cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
romanbull
Frequent Visitor

calculate employee break time

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. 

 

DateTimeDoorName
1-Jun6/1/2018 18:18Door1AK
1-Jun6/1/2018 18:19Door3AK
1-Jun6/1/2018 18:29Door3AK
1-Jun6/1/2018 18:29Door1AK
1-Jun6/1/2018 18:51Door1AJ
1-Jun6/1/2018 18:51Door3AJ
1-Jun6/1/2018 19:00Door1AK
1-Jun6/1/2018 19:01Door3AK
1-Jun6/1/2018 20:28Door3AK
1-Jun6/1/2018 20:49Door3AK
1-Jun6/1/2018 20:58Door3AK
1-Jun6/1/2018 20:59Door1AK
1-Jun6/1/2018 21:47Door1AK
1-Jun6/1/2018 21:47Door3AK
1-Jun6/1/2018 21:53Door3AJ
1-Jun6/1/2018 21:54Door3AJ
1-Jun6/1/2018 21:54Door2AJ
1-Jun6/1/2018 21:55Door1AJ
1-Jun6/1/2018 22:11Door1AJ
1-Jun6/1/2018 22:15Door1AJ
1-Jun6/1/2018 22:16Door3AJ
1-Jun6/1/2018 23:17Door3AK
1-Jun6/1/2018 23:42Door1AK
1-Jun6/1/2018 23:43Door3AK
1-Jun6/2/2018 0:11Door3AJ
1-Jun6/2/2018 0:11Door2AJ
1-Jun6/2/2018 0:14Door3AJ
1-Jun6/2/2018 1:45Door3AJ
1-Jun6/2/2018 1:45Door2AJ
1-Jun6/2/2018 1:46Door2AJ
1-Jun6/2/2018 1:46Door3AJ
1-Jun6/2/2018 2:46Door3AK
1-Jun6/2/2018 2:47Door1AK
1-Jun6/2/2018 4:06Door3AJ
1 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.

View solution in original post

5 REPLIES 5
yppd
Frequent Visitor

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

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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?

 

 image.png

Manually, I tried to document the breaktime in excel.  Not sure how this can be coded in powerbi to get similar kind of result.

 

Manual.JPG

 

 

HI @romanbull

 

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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.

Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.