Skip to main content
cancel
Showing results for 
Search instead 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.

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

 

Test case.png

 

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

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

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:

Eyelyn9_0-1659335076603.png

 

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.

View solution in original post

4 REPLIES 4
v-eqin-msft
Community Support
Community Support

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:

Eyelyn9_0-1659335076603.png

 

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?

multiple countries.png

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

 

 

Mohan1029_0-1658931234886.png

Then lookup that column in table1 based on country.

 

 

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

 

 

Mohan1029_1-1658931304491.png

 

 

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.

Test case.png

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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