Find the nearest date from another table based on ID

[darianle]

https://www.dropbox.com/s/9es5uzaxjtrjm9l/SampleDATA.pbix?dl=0

Hello,

I'm looking for the closest date in (Table1) to the value (in Table2) based on the ID. 

[the date should be greater than and not equal to]

I'm using the dax below, but the results are finding the closest value and ignoring the ID.

TIME2 =

    VAR enter_ = Table2[MessageDate]

    RETURN

        TOPN(

            1,

            CALCULATETABLE(

                DISTINCT(Table1[MessageDate2]),

                ALLEXCEPT(Table1, Table1[ID]),

                Table1[MessageDate2] > enter_

            ),

         Table1[MessageDate2] - enter_, asc)

please help, 

Expected Results:

[darianle]

works perfectly. thanks

[v-jianboli-msft · Accepted solution]

Hi @darianle ,

Please try:

Column =
VAR _a =
    MINX (
        FILTER (
            'Table1',
            [ID] = EARLIER ( Table2[ID] )
                && DATEDIFF ( [MessageDate], [MessageDate2], MINUTE ) > 0
        ),
        DATEDIFF ( [MessageDate], [MessageDate2], MINUTE )
    )
RETURN
    MINX (
        FILTER (
            'Table1',
            [ID] = EARLIER ( Table2[ID] )
                && DATEDIFF ( [MessageDate], [MessageDate2], MINUTE ) = _a
        ),
        [MessageDate2]
    )

Final output:

Best Regards,

Jianbo Li

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