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

John2029 
Create mindate column in Table2 using below dax

 

MinDate = CALCULATE(MIN(CountryTable2[Date]),ALLEXCEPT(CountryTable2,CountryTable2[Country]))

 

 

Then lookup that column in table1 based on country.

 

 

MinDate = 
LOOKUPVALUE(CountryTable2[MinDate],CountryTable2[Country],CountryTable1[Country])

 

 

 

 

Accept the solution if it works for you.

 

Thanks,

Mohan V.

 

John2029's avatar
John2029
Frequent Visitor
4 years ago

Hi Mohan V.,

Thank you very much for taking the time to look into my question. If I understand well, your solution calculates the minimum date for each country in table 2 and then it is retrieved via a vlookup in table 1.
What I am trying to do is a bit different, I am trying to find the closest date in table 2 to a date in table 1 for each country.
In the example below the date for Afghanistan in table 1 is 01.08.2018. The closest date in Table 2 would be 31.08.2018.

 

Thank you again very much for your help (and I did already learned something with the vlookup solution above).