Calculate difference between two rows with dependencies

[backflash]

Hey All,

hope someone can help me with the following:

I want to calculate the difference between two rows - but I want to do this only for rows where some Column-values are the same.

(btw: I already checked out the other posts about this topic: I saw the idea with the two indexes (one from 0, the other from1) and the usage of the EARLIER function - but I cannot apply this to my case - but maybe someone has an idea how to merge it all together 😉 )

I have a table with the following columns: eomployee number, date, start, end.

The table contains the working start and end time stamp of each employ on each day. The columns for start and end are formatted as datatype 'time' (short-time).

I want to calculate the duration of the breaks - for each employee on each day separately for each break.

That means, I need to substract the end time of the row above from the start time of the next line.

In the example for employee number 44, on 01.01.2022 we would have:

break 1: 14 min. (10:45 - 10:31)

break 2: 40 min. (13:10 - 12:30)

I want to create a new column that will contain this break durations, but it should recognize when there is a new day and also when there is a new employee - that means it will leave some cells empty when calculating.

And I am struggling with this condition 'only for same day and only for same employee'

Do I have to do the way with two indexes and a merge and calculate it within a new column and compare both, the date and the employee values to prevent calculating breaks between days ... or is there a better way with DAX?

Hope someone has a nicer way to do this.

Thanks in advance!

Regards Vanessa

[tamerj1]

Hi @backflash 

you create a calculated column

Break Time =
VAR CurrentStart = Table[Start]
VAR CurrentEnd = Table[End]
VAR PreviousEnd =
    MAXX (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Emplyee Number], Table[Date] )
            ),
            Table[End] < CurrentEnd
        ),
        Table[End]
    )
RETURN
    CurrentStart - PreviousEnd

[backflash]

Hey @tamerj1 ,

thanks a lot - your code for the calculated column is working fine.

But I now have a break time on each new day (from 0:00 to start time)  - do you have an idea how I could have the cells of the break time empty, when there is a new day?

[tamerj1 · Accepted solution]

Hi @backflash 

Break Time =
VAR CurrentStart = Table[Start]
VAR CurrentEnd = Table[End]
VAR PreviousEnd =
    MAXX (
        FILTER (
            CALCULATETABLE (
                Table,
                ALLEXCEPT ( Table, Table[Emplyee Number], Table[Date] )
            ),
            Table[End] < CurrentEnd
        ),
        Table[End]
    )
RETURN
    IF ( 
        NOT ISBLANK ( PreviousEnd ),
        CurrentStart - PreviousEnd
    )

[backflash]

Many thanks @tamerj1 perfect!