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.

kmeadows123's avatar
kmeadows123
Regular Visitor
2 years ago

John2029 Hello! Sorry to dig this up, but I am dealing with the exact same situation. Did you find the proper solution to find the closest dates between the two tables, leaving the duplicates rows?