Find Closest Matching Date based on matching ID code from 2 tables

[Pandadev]

I am trying to compare the start and end events from 2 tables , both tables are joined by a matching ID , I am trying to find the closest matching date in table table 2 for each row in table 1.

Example below would match ID 01 Table 15/12/2020   26/12/2020 withTable 2 - 13/12/2020   25/12/2020 as that is closest match

Table 1

ID     Start Date      End Date

01     15/12/2020   26/12/2020

02     09/12/2020   18/12/2020

Table 2

ID     Start_Date      End_Date

01     15/06/2020   22/06/2020

01     15/08/2020   11/07/2020

01     15/07/2020   10/08/2020

01     15/10/2020   11/11/2020

01     13/12/2020   25/12/2020

01     29/12/2020   05/01/2021

02     09/12/2020   18/12/2020

[mahoneypat]

Here is a measure expression that checks start dates in Table 2 before and after the Table 1 start date, and returns the closer one.

Closest Start =
VAR vThisDate =
    MIN ( Table1[Start Date] )
VAR vMinAfter =
    CALCULATE (
        MIN ( Table2[Start_date] ),
        Table2[Start_date] >= vThisDate
    )
VAR vMaxBefore =
    CALCULATE (
        MAX ( Table2[Start_date] ),
        Table2[Start_date] <= vThisDate
    )
RETURN
    IF (
        DATEDIFF (
            vThisDate,
            vMinAfter,
            DAY
        )
            < DATEDIFF (
                vMaxBefore,
                vThisDate,
                DAY
            ),
        vMinAfter,
        vMaxBefore
    )

Pat

[Pandadev]

Thanks , Pat , is it possible to include the ID in the measure , as I need the closest matching date based on ID

[v-lionel-msft]

Hi @Pandadev ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pandadev]

Hi I added your measures from the attached pbix , but for some reason it is not working correctly , my ID codes are a range of number both negative and positive values , like 346722 and -1381 , would this be why it is not working as in the example the id codes were 1 and 2 .

[v-lionel-msft]

Hi @Pandadev ,

It should have nothing to do with the value of ID. I found that the following two measures(__end_min&__start_min) must be placed in Table2, otherwise an error will be reported. Did you do this?

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Pandadev]

Hi Thanks for looking at this , is it possible to adapt this to just check the start date , as I forgot that one table does not have an end date in it , thanks