Forum Discussion
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,
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] + _minDiffOutput:
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.
5 Replies
John2029
Create mindate column in Table2 using below daxMinDate = 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.
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).
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] + _minDiffOutput:
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.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,
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?
