cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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?

 

 

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.

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors