finding next date in seperate column

[mik618]

Hi, wondering if anyone can help with this,

I've got quite a mess of tables with meeting dates and trying to get my head round the time fram the user had to wait from going from one meeting to the next..

I have two tables, table1 and table2, linked by UserID, both have a MeetingDate and MeetingType column, what i need to do is get the date difference between MeetingDates for MeetingType A in table 1, and B in table2.

to complicate things further, Users might have had multiple meeting A and Meeting Bs, as after meeting b they can be referred back to meeting a... so for example the table might look like this for one user;

userID     table1[MeetingDate]      table1[MeetingType]      table2[MeetingDate]      table2[MeetingType] 

1                    01/01/2018                           a                                10/02/2015                          c

1                    01/02/2018                           a                                10/01/2018                          b

1                    01/04/2018                           d                                10/03/2018                          b 

In this example the column i would like to add would show there was 9 days difference between 01/01/2018 meeting a and the 10/01/2018, and 36 days between 01/02/2018 meeting a and 10/03/2018 meeting b

I just can't get my head round linking them up, any help would be much appreciated..

[Anonymous · Accepted solution]

HI @mik618 .

Maybe you can try to use following measure to lookup second table for next date.

Measure =
VAR currId =
    MAX ( Table1[userID] )
VAR currDate =
    MAX ( Table1[MeetingDate] )
RETURN
    CALCULATE (
        MIN ( Table2[MeetingDate] ),
        FILTER ( ALLSELECTED ( Table2 ), [MeetingDate] > currDate && [userID] = currId )
    )

Regards,

Xiaoxin Sheng

[mik618]

Hi Xiaoxin, 

Thanks so much for this, looks like this is is what i need!