Forum Discussion
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:
V_EWS_TASKRECORD table
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
HI kattlees
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") )This is really close. Here is my column:
MD Down Time =
VAR LastEndTime =
MAXX (
FILTER(
V_EWS_TASK_RECORD,
V_EWS_TASK_RECORD[OR Times Final.SURGEON] = EARLIER(V_EWS_TASK_RECORD[OR Times Final.SURGEON]) &&
V_EWS_TASK_RECORD[MD out] < EARLIER(V_EWS_TASK_RECORD[MD In])
),
V_EWS_TASK_RECORD[MD out])VAR StartTime = V_EWS_TASK_RECORD[MD In]
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.
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
