Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kattlees
Post Patron
Post Patron

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
Phil_Seamark
Employee
Employee

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!

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.