Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

calculating working time in office

Hi all,    I would like to calculate the elapsed time in office by whole day for each worker ID.    How can i calculate this without the time spended at outside   thnx  
Need Help
  • PattemManohar's avatar
    PattemManohar
    7 years ago

    Anonymous Please try this as a "New Column" which will give the each breaktime difference between OUT and IN time

     

    BreakDuration = 
VAR _InTime = CALCULATE(MAX(Test95BreakTime[Time]),FILTER(ALL(Test95BreakTime),Test95BreakTime[Time] < EARLIER(Test95BreakTime[Time]) && Test95BreakTime[WorkerID] = EARLIER(Test95BreakTime[WorkerID]) && Test95BreakTime[Direction] <> EARLIER(Test95BreakTime[Direction]) && Test95BreakTime[Direction] = "OUT"))
RETURN DATEDIFF(TIME(HOUR(_InTime),MINUTE(_InTime),SECOND(_InTime)),Test95BreakTime[Time],MINUTE)

     

    Then, you can create a measure to get the total worked/logged time excluding the break time using the below as "Measure"

     

    Test95TotalLoggedTime = 
VAR _BreakTime = SUM(Test95BreakTime[BreakDuration])
VAR _FirstIn = CALCULATE(MIN(Test95BreakTime[Time]),Test95BreakTime[Direction]="IN")
VAR _LastOut = CALCULATE(MAX(Test95BreakTime[Time]),Test95BreakTime[Direction]="OUT")
RETURN DATEDIFF(_FirstIn,_LastOut,MINUTE)-_BreakTime

     

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity Champion
7 years ago

Anonymous Please try this...

 

Add an additional Rank field as below

 

Rnk = RANKX(FILTER(ALL(Test95BreakTime1),Test95BreakTime1[WorkerID]=EARLIER(Test95BreakTime1[WorkerID]) && Test95BreakTime1[Date]=EARLIER(Test95BreakTime1[Date])),Test95BreakTime1[Time],,ASC,Dense)

Now change the BreakDuration logic as below ( Just added additional Rnk condition)

 

BreakDuration = 
VAR _InTime = CALCULATE(MAX(Test95BreakTime1[Time]),FILTER(ALL(Test95BreakTime1),Test95BreakTime1[Time] < EARLIER(Test95BreakTime1[Time]) && Test95BreakTime1[WorkerID] = EARLIER(Test95BreakTime1[WorkerID]) && Test95BreakTime1[Direction] <> EARLIER(Test95BreakTime1[Direction]) && Test95BreakTime1[Date] = EARLIER(Test95BreakTime1[Date]) && Test95BreakTime1[Direction] = "OUT" && Test95BreakTime1[Rnk]<>1))
RETURN DATEDIFF(TIME(HOUR(_InTime),MINUTE(_InTime),SECOND(_InTime)),Test95BreakTime1[Time],MINUTE)

That's it !!! It should exclude the First Out of the day (ideally it should be IN but for some hard working staff who works over night, their first entry will OUT for the day)

 

 

Please Note - You need to tweak the measure calculation as well to exclude the first entry is OUT for the day

Anonymous's avatar
Anonymous
Not applicable
7 years ago

PattemManoharsorry for the delayed response. All works fine but I need a last tune. 

 

If the worker exited after the midnight than the calculation crashes for the previous day. How can i change the "Test95TotalLoggedTime" logic to addept request below ? 

 

"If the last movement is (IN) for the current day, assume the last (OUT) was 23:59 and calculates the difference between the last (IN) time and 23:59 as worked time."