Find the Nearest Value One column to Another Based on ID

[darianle]

Good Morning,

I have been wrapping my head around this for days—numerous searches to no avail.

I have three columns:   ID, Enter, and Exit

For Each ID,  if there is an Enter Time, there should be a corresponding Exit Time (nearest Value).

Could anyone provide a Dax for this?

In my sample below, I have provided an output column as to what the expected results should be.

thanks!

Sample Date

https://www.dropbox.com/s/h6dnh4h17luz50l/Sample%20Data.xlsx?dl=0

[AlB · Accepted solution]

Hi @darianle 

Try this calculated column:

ClosestExit = 
VAR enter_ = Table1[Enter]
RETURN
    TOPN (
        1,
        CALCULATETABLE (
            DISTINCT ( Table1[Exit] ),
            ALLEXCEPT ( Table1, Table1[NumberID] ),
            Table1[Exit] >= enter_
        ),
        Table1[Exit] - enter_, ASC
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[darianle]

WoW! that worked great.. 

The only issue now is: 

"<ccon>There's not enough memory to complete this operation. Please try again later when there may be more memory available.</ccon>"

But that's on me.

Thanks again