Forum Discussion
finding next date in seperate column
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..
- Anonymous7 years ago
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
2 Replies
- AnonymousNot applicable
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
Helper I
Hi Xiaoxin,
Thanks so much for this, looks like this is is what i need!