Forum Discussion

darianle's avatar
darianle
Regular Visitor
3 years ago
Solved

Find the nearest date from another table based on ID

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 th...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    3 years ago

    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.

darianle's avatar
darianle
Regular Visitor
3 years ago

works perfectly. thanks