Forum Discussion
calculating working time in office
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
Community ChampionAnonymous I just copied your sample data and the data types are detected automatically, here is the datatypes I've
WorkedID - Whole Number
Direction - Text
Date - Date
Time - Time
Hi PattemManohar, it is my fault. Now it works.
i have checked the results and calculation still crashes for spesific days.
Previous date's "afrer-midnight" records are effecting the calculation.
You can analysis below record for example. It starts with an OUT record but actually it is previous date's movement.
It i s a hard scanario to cover %100. Is there any idea to eliminate these OUT records ?
may be we can include next day's first "after- midnight OUT record" to calculation of calculated day? And disregard other records before 06:00 ?
| Worker ID | Direction | Date | Time | BreakDuration |
| 1 | OUT | 10.08.2018 | 03:39:04 | |
| 1 | IN | 10.08.2018 | 08:43:26 | 304 |
| 1 | OUT | 10.08.2018 | 13:22:01 | |
| 1 | IN | 10.08.2018 | 14:33:50 | 71 |
| 1 | OUT | 10.08.2018 | 18:12:56 | |
| Worker ID | Direction | Date | Time | BreakDuration |
| 1 | OUT | 15.08.2018 | 00:49:41 | |
| 1 | IN | 15.08.2018 | 11:42:49 | 653 |
| 1 | OUT | 15.08.2018 | 12:39:35 | |
| 1 | IN | 15.08.2018 | 13:34:55 | 55 |
| 1 | OUT | 15.08.2018 | 17:56:34 |
- PattemManohar
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
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."