Display closest date between 2 date columns from 2 different tables

[John2029]

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 table 1,
2) a column dates from table 1,
3) a column "closest date" with date from table 2 that is the closest to the corresponding date in table 1

For example in the example below Afghanistan has date 01.08.2018 in table 1
In table 2 Afghanistan has 8 lines with different dates and the closest date to 01.08.2018 (from table 1) would be 31.08.2018. 
So I am trying to display this date.
I was thinking to create a column calculating the difference in days between dates and displaying the one with the smallest gap. I also tried to create a measure but did not manage to retrieve the dates from 2 different tables (and I was using the calculate median to retried the number of days which does not seem to work for dates) . 

Any help would be much appreciated 🙂 
Thank you and have a nice day,

[kmeadows123]

@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?

[v-eqin-msft · Accepted solution]

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]

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,

[Anonymous]

@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]

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