cancel
Showing results for
Did you mean:
Member

## Get value for next date based on a condition

I need to get a time value from another row to get a time difference.

Example:

Fields with examples are

Date             Surgeon      Start Time     End Time

2-1-2018        Jim               07:00             08:25

2-1-2018        Jane             07:15              08:00

2-1-2018        Jane             09:00             10:45

2-1-2018        Jim               09:00             11:00

2-1-2018         Jane            11:00              11:45

2-1-2018         Jim              11:15              13:00

2-2-2018         Jim              07:00              10:00

2-2-2018          Jim             10:05               12:30

I need to get the time difference  from End Time to Start Time of next case. So for each surgeon

Jim on 2-1-2018 - Diff from 8:25 to 09:00

Diff from 11:00 to 11:15

On 2-2-2018 Diff from 10:00 to 10:05

Jane - Diff from 08:00 to 09:00

Diff from 10:45 to 11:00

3 REPLIES 3
Super User

## Re: Get value for next date based on a condition

This calculated column might be close

```Column =
VAR LastEndTime =
MAXX (
FILTER(
'Table2',
'Table2'[Surgeon] = EARLIER('Table2'[Surgeon]) &&
'Table2'[End Time] < EARLIER('Table2'[Start Time])
),
'Table2'[End Time])

VAR StartTime = 'Table2'[Start Time]
RETURN IF(
NOT ISBLANK(LastEndTime),
-- THEN --
FORMAT(StartTime - LastEndTime,"HH:mm")
)```

Proud to be a Datanaut!

Member

## Re: Get value for next date based on a condition

This is really close. Here is my column:

MD Down Time =
VAR LastEndTime =
MAXX (
FILTER(
),

RETURN IF(
NOT ISBLANK(LastEndTime),
-- THEN --
FORMAT(StartTime - LastEndTime,"HH:mm")
)

Here are results
(red are what I need i to be)

Top row would be 8:04 - 8:58

2nd Row would be 9:19 to 10:34

etc.

Community Support Team

## Re: Get value for next date based on a condition

Hi @kattlees,

Maybe you just need to adjust part of the formula to "NextStartDate". The report table visual should have a proper order due to the new column from the source table.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.