Forum Discussion

John2029's avatar
John2029
Frequent Visitor
4 years ago
Solved

Display closest date between 2 date columns from 2 different tables

Hello,  I have 2 tables.  Table 1: column countries andcolumn dates Table 2: column countries and column dates I am trying to create a table where I display 3 columns: 1) a column countries from...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi John2029 ,

     

    Please try:

    Date Tab 2 = 
VAR _t =
    ADDCOLUMNS (
        'Table 2',
        "DateDiff",
            VAR _tab1 =
                LOOKUPVALUE ( 'Table 1'[Date Tab 1], [Country], [Country] )
            RETURN
                DATEDIFF ( [Date Tab 2], _tab1, DAY )
    )
VAR _minDiff =
    MINX ( FILTER ( _t, [Country] = EARLIER ( 'Table 1'[Country] ) ), ABS([DateDiff]))
RETURN
    [Date Tab 1] + _minDiff

    Output:

     

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi John2029 ,

 

Please try:

Date Tab 2 = 
VAR _t =
    ADDCOLUMNS (
        'Table 2',
        "DateDiff",
            VAR _tab1 =
                LOOKUPVALUE ( 'Table 1'[Date Tab 1], [Country], [Country] )
            RETURN
                DATEDIFF ( [Date Tab 2], _tab1, DAY )
    )
VAR _minDiff =
    MINX ( FILTER ( _t, [Country] = EARLIER ( 'Table 1'[Country] ) ), ABS([DateDiff]))
RETURN
    [Date Tab 1] + _minDiff

Output:

 

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • John2029's avatar
    John2029
    Frequent Visitor
    4 years ago

    Dear Evelyn9,

    Thank you very much for your help and for this solution. This is very helpful ! I am sorry in advance to ask if you would you have a suggestion if in Table 1 there would be multiple countries with the same name?
    For example if there was two countries with the name Afghanistan? As I understand the countries in table 1 must have a unique name or you would get the message: "A table of multiple values was supplied where a single value was expected."
    I wonder if there would be a possibility to have the following output?

    Thank you, and thank you again very much for your initial solution that answers the initial question. 
    Best regards,