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
@daglaroglu 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
@daglaroglu 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
@daglaroglu Thanks for providing the sample data.
Here is the new logic to handle mutliple dates,
Add a new column as below (Added an another condition to check the date as well)
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")) RETURN DATEDIFF(TIME(HOUR(_InTime),MINUTE(_InTime),SECOND(_InTime)),Test95BreakTime1[Time],MINUTE)
Create a measure as below
Test95TotalLoggedTime1 = VAR _BreakTime = SUM(Test95BreakTime1[BreakDuration]) VAR _FirstIn = FORMAT(MIN(Test95BreakTime1[Time]),"hh:mm:ss") VAR _LastOut = FORMAT(MAX(Test95BreakTime1[Time]),"hh:mm:ss") RETURN DATEDIFF(_FirstIn,_LastOut,MINUTE)-_BreakTime
@daglaroglu 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
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
