cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kattlees Member
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:

 

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 3
Super User
Super User

Re: Get value for next date based on a condition

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")
        )

image.png


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

kattlees Member
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(
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.

 

ScreenShot.PNG

Community Support Team
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.