cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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 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,

1 ACCEPTED SOLUTION
Community Support

Hi @John2029 ,

``````Date Tab 2 =
VAR _t =
'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.

4 REPLIES 4
Community Support

Hi @John2029 ,

``````Date Tab 2 =
VAR _t =
'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.

Frequent Visitor

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
Not applicable

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

Frequent Visitor

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors