topic Calculate difference between two rows with dependencies in DAX Commands and Tips

Calculate difference between two rows with dependencies

backflash — Fri, 22 Apr 2022 16:24:26 GMT

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

Re: Calculate difference between two rows with dependencies

tamerj1 — Fri, 22 Apr 2022 16:39:19 GMT

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

Re: Calculate difference between two rows with dependencies

backflash — Mon, 25 Apr 2022 07:18:40 GMT

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?

Re: Calculate difference between two rows with dependencies

tamerj1 — Mon, 25 Apr 2022 07:45:05 GMT

Hi backflash

Re: Calculate difference between two rows with dependencies

backflash — Mon, 25 Apr 2022 08:07:42 GMT

Many thanks tamerj1 perfect!