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
I tried the new formula but "BreakDuration" gave en error : DAX comparison values of type Date with values of type Text.
Consider the VALUE or FORMAT function to convert one of the values.
In my table, "date" column's data type is date. When i changed it's data type to text the formula didnt give an error but also didnt return any result.
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."