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 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:

 

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.

2 Replies

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    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.