Forum Discussion

mik618's avatar
mik618
Icon for Helper I rankHelper I
7 years ago
Solved

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..

  • Anonymous's avatar
    Anonymous
    7 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

  • Anonymous's avatar
    Anonymous
    Not 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's avatar
      mik618
      Icon for Helper I rankHelper I

      Hi Xiaoxin, 

       

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