Elapsed time between previous end time and next start time for all employees

[Blues88]

Hello,

I have a list of employees along with the time they started and stopped picking cases during their shift. After their "Pick End Time" they drive to their next pick location and begin picking again ("Pick Start Time").

I already have elapsed time between "Pick Start Time" and "Pick End Time", but I need to capture the "Elapsed Time Between Picks" to get a full picture of how much time they spent actively working, = (elapsed time of pick start to pick end) + (elapsed time between picks).

I want to have the elapsed time bewteen picks for all employees in the data set.

NOTE: the shifts can go from PM one day into AM the next day. Not sure if that'll be problematic in the solution.

[v-luwang-msft · Accepted solution]

Hi @Blues88 ，

Test the below dax to create column:

Column = 
VAR test =
    CALCULATE (
        MAX ( 'Table'[Pick End Time] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Employee] = EARLIER ( 'Table'[Employee] )
                && 'Table'[Shift Number] = EARLIER ( 'Table'[Shift Number] )
                && EARLIER ( 'Table'[Pick Start Time] ) > 'Table'[Pick Start Time]
        )
    )
VAR test2 =
    DATEDIFF ( test, 'Table'[Pick Start Time], SECOND )
RETURN
    test2 / 60

Column 2 = 'Table'[Column]+'Table'[Elapsed Time]

I have compared the script data with the screenshot data you provided and the data is consistent.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[amitchandak]

@Blues88 , Try a new column like

new column =
var _1 = maxx(filter(Table, [Employee #] = earlier([Employee #]) && [Pick End Time] <earlier([Pick End Time])),[Pick End Time])
return
datediff([Pick start Time],_1, second)/60

[Blues88]

Seems very close, but when I filter on one single employee to check the results, some results are matching, and some results are not.

Also, why would JASON have an elapsed time between picks be 4.383333333 (H2) if his first pick of the night was 12:02:07 AM with no prior picks?

Here's another example of filtering on one single employee and checking the results. Some work, many do not match.

[Blues88]

@amitchandak 

I also have a date column and shift number column. Do you need to include those in your filter too?

The shift runs from PM one night to AM into the next day. The way we track that shift is by shift number. Example, shift 1805 goes from 8/31 PM to 9/1 AM and then shift 1806 is 9/1 PM to 9/2 AM. 

Can you include that in the solution and maybe that will fix the inconsistencies in the previous result?

Thank you in advance!