How to Deduct Break Times and Lunch Times From Time Stamps

[arees]

Hello,

I’m trying to calculate an average production time of an item by looking at a list of time stamps and then calculating the average time between each one. I need to deduct out break times and lunch times from these time stamps and am having a hard time coming up with the right calculation. Our company takes breaks at 9:00 -9:15 AM, 12:00 - 12:30 PM and 3:00 - 3:15 PM. Is there a formula to recognize if a series of time stamps includes those break times, then to deduct out the 15 or 30 minutes from the total time? Here is an example of the data:

QgzQEMvOX.png)

Thanks!
I

[Greg]

Hi @arees.

As with many things in Power BI, there are often multiple ways of solving a problem. You didn’t provide a dataset or a work-in-progress PBIX file, so I whipped-up a simple sample dataset with 10 tasks and a simple solution (verbose, and probably not at all performant, but hopefully easy to read).

Here’s what I did:

• created measures for [Task Start DateTime] and [Task Finish DateTime]
• created a [Gross Minutes] measure using measure branching and DATEDIFF to calculate the gross task time
• created a [Break Minutes] measure (here’s the really verbose one) using SWITCH TRUE and DATEDIFF to calculate the break time to deduct
• created a [Net Minutes] measure using measure branching and simple subtraction to calculate the net task time

---

Task Start DateTime = 
VAR _TaskID = SELECTEDVALUE( 'Task Times'[Task] ) 
VAR _Date = SELECTEDVALUE( 'Task Times'[Date] )
VAR _EmployeeID = SELECTEDVALUE( 'Task Times'[Employee] )
VAR _Time = 
CALCULATE( MAX( 'Task Times'[Time] ),
    FILTER( 'Task Times',
        'Task Times'[Task] = _TaskID &&
        'Task Times'[Employee] = _EmployeeID &&
        'Task Times'[Status] = "Start" )
) 
VAR _Result = _Date + _Time

RETURN
_Result

Task Finish DateTime = 
VAR _TaskID = SELECTEDVALUE( 'Task Times'[Task] ) 
VAR _Date = SELECTEDVALUE( 'Task Times'[Date] )
VAR _EmployeeID = SELECTEDVALUE( 'Task Times'[Employee] )
VAR _Time = 
CALCULATE( MAX( 'Task Times'[Time] ),
    FILTER( 'Task Times',
        'Task Times'[Task] = _TaskID &&
        'Task Times'[Employee] = _EmployeeID &&
        'Task Times'[Status] = "Finish" )
) 
VAR _Result = _Date + _Time

RETURN
_Result

Gross Minutes = DATEDIFF( [Task Start DateTime],[Task Finish DateTime], MINUTE)

Break Minutes = 
VAR _TaskID = SELECTEDVALUE( 'Task Times'[Task] ) 
VAR _Task_StartTime = 
CALCULATE( MAX( 'Task Times'[Time] ),
    FILTER( 'Task Times',
      'Task Times'[Task] = _TaskID &&
      'Task Times'[Status] = "Start" )
)
VAR _Task_FinishTime = 
CALCULATE( MAX( 'Task Times'[Time] ),
    FILTER( 'Task Times',
      'Task Times'[Task] = _TaskID &&
      'Task Times'[Status] = "Finish" )
) 
VAR _Break_1_StartTime = 
CALCULATE( MAX( 'Break Times'[Break Start] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 1 )
)
VAR _Break_1_EndTime = 
CALCULATE( MAX( 'Break Times'[Break End] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 1 )
)
VAR _Break_2_StartTime = 
CALCULATE( MAX( 'Break Times'[Break Start] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 2 )
)
VAR _Break_2_EndTime = 
CALCULATE( MAX( 'Break Times'[Break End] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 2 )
)
VAR _Break_3_StartTime = 
CALCULATE( MAX( 'Break Times'[Break Start] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 3 )
)
VAR _Break_3_EndTime = 
CALCULATE( MAX( 'Break Times'[Break End] ),
    FILTER( 'Break Times',
      'Break Times'[Row ID] = 3 )
)
VAR _Break_1_Minutes = 
SWITCH( TRUE(),
    _Task_StartTime <= _Break_1_StartTime && 
    _Task_FinishTime >= _Break_1_EndTime, DATEDIFF( _Break_1_StartTime, _Break_1_EndTime, MINUTE ),
    0
)
VAR _Break_2_Minutes = 
SWITCH( TRUE(),
    _Task_StartTime <= _Break_2_StartTime && 
    _Task_FinishTime >= _Break_2_EndTime, DATEDIFF( _Break_2_StartTime, _Break_2_EndTime, MINUTE ),
    0
)
VAR _Break_3_Minutes = 
SWITCH( TRUE(),
    _Task_StartTime <= _Break_3_StartTime && 
    _Task_FinishTime >= _Break_3_EndTime, DATEDIFF( _Break_3_StartTime, _Break_3_EndTime, MINUTE ),
    0
)

VAR _Result = _Break_1_Minutes + _Break_2_Minutes + _Break_3_Minutes

RETURN
_Result

Net Minutes = [Gross Minutes] - [Break Minutes]

---

Hope this helps.

Greg
Task and Break Times.xlsx (10.8 KB)
eDNA Forum - Task Times with Breaks Deducted.pbix (32.0 KB)

[Harsh]

Hello @arees,

Thank You for posting your query onto the Forum.

In order to deduct Break Times from the Time Stamps. Firstly, you need to convert them into the intervals i.e., Start Time and End Time.

That is, from this -

To this -

Once data is transformed into Start and End time intervals, then you need to write the below provided DAX measure to achieve the results -

Total Minutes (Exc. Break Times) = 
SUMX(
    ADDCOLUMNS(
        SUMMARIZE(
            Data , 
            Data[EmployeeID] , 
            Data[Date] , 
            Data[Start Time] , 
            Data[End Time] ) , 
        "@Total_Minutes" , 
        SWITCH( TRUE() , 
            TIME( 9 , 0 , 0 ) >= Data[Start Time] && 
            TIME( 9 , 15 , 0 ) <= Data[End Time] ,
            DATEDIFF( Data[Start Time] , Data[End Time] , MINUTE ) - 15 , 

            TIME( 12 , 0 , 0 ) >= Data[Start Time] && 
            TIME( 12 , 30 , 0 ) <= Data[End Time] ,
            DATEDIFF( Data[Start Time] , Data[End Time] , MINUTE ) - 30 ,

            TIME( 15 , 0 , 0 ) >= Data[Start Time] && 
            TIME( 15 , 15 , 0 ) <= Data[End Time] ,
            DATEDIFF( Data[Start Time] , Data[End Time] , MINUTE ) - 15 , 

        DATEDIFF( Data[Start Time] , Data[End Time] , MINUTE ) ) ) ,
    [@Total_Minutes] )

I’m also attaching the working of the PBIX file for the reference purposes. Please go through the steps applied in the Power Query to review them.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Deducting Break Times From Time Stamps - Harsh.pbix (22.8 KB)

[EnterpriseDNA]

Thank you for the detailed answers here @Greg and @Harsh

Hello @arees,

Did the response above help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer that helped you as the SOLUTION.

Thank you

[EnterpriseDNA]

Hi @arees ,

Due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

Thanks to the contributors of this post.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!

[arees]

Thank you very much Harsh! This worked for me!