Time difference between rows - calculating break times

[matkvaid]

Hi, need Your help again... I want to find if this is possible:I have list of operations with begin time and end time. Goal is to find time not in operation and calculate break count and times (if employee is more than 10 min off operations then he is on a break). Then further is counting breaks and calculating break time (i think this is a bit too much to follow when the break started - like if the break was on break time or not).What i found is creating index column, but i dont think i can use it here, as times has to be calculated per employee. Attaching sample data and what i want to find, You people have some ideas? Big thanks in advance

[matkvaid]

Still trying to find solution, tried use summarize but something does not work here, is this far from solution?

TOP =

    VAR currentnumber = ActivityHeader[No_]

    VAR currentday = ActivityHeader[Assignment Date]

    VAR currentemployee = ActivityHeader[Assigned User ID]

    VAR TempTable = Filter(

                SUMMARIZE(ActivityHeader, "USER", ActivityHeader[Assigned User ID], "nr",  ActivityHeader[No_], "ENDTIME", MAX(ActivityHeader[End Assignment Time])), ActivityHeader[Assigned User ID] = currentemployee && ActivityHeader[Assignment Date] = currentday && ActivityHeader[No_] < currentnumber)

    Return

       MAXX(TOPN(1, TempTable, ActivityHeader[No_] < currentnumber), [ENDTIME])

[v-binbinyu-msft]

Hi @matkvaid ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[matkvaid]

Not sure how to correctly explain - i want to calculate time from last end to current start times:

[v-binbinyu-msft]

Hi @matkvaid ,

Please try below steps:

1. below is my test table
Table:

2. add two column with below dax formula

Index =
RANKX ( 'Table', [Start Time],, ASC )

Break Time =
VAR cur_index = [Index]
VAR cur_st = [Start Time]
VAR tmp =
    FILTER ( ALL ( 'Table' ), 'Table'[Index] = cur_index - 1 )
VAR pre_et =
    CALCULATE ( SELECTEDVALUE ( 'Table'[End Time] ), tmp )
VAR diff =
    DATEDIFF ( pre_et, cur_st, SECOND )
RETURN
    diff

Please refer the attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[matkvaid]

Problem is that there is many employes, and many days, so just sorting by time does not work - index duplicates for many rows, as same start time can be on many days, etc - so this does not work 😞 My start time has 82k distinct values in 4 mil rows 

[matkvaid]

Uploaded excel example here: - https://wetransfer.com/downloads/545c9d8c15679794e23625f2a9d9dfb620220907071037/de1861ea6c5a98e84e24...

[PowerUserR]

Hi,
Could you please attach a copy of the actual table? That is, in text and not picture.