Forum Discussion

Mythicos's avatar
Mythicos
Frequent Visitor
2 years ago
Solved

Finding another value based on ID and nearest date

I have a table named "Event", with an Event ID named "UPN", a User ID named "HMR" and an Event Date named "Dt".   Here's a small sample of the table:     I want to add a column named "UPN ...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    please check the below picture and the attached pbix file.

     

     

    OFFSET function (DAX) - DAX | Microsoft Learn

     

    UPN previous CC =
VAR _t =
    FILTER ( Event, Event[HMR] = EARLIER ( Event[HMR] ) )
RETURN
    MAXX (
        OFFSET (
            -1,
            _t,
            ORDERBY ( Event[dt], ASC ),
            ,
            ,
            MATCHBY ( Event[UPN], Event[HMR], Event[dt] )
        ),
        Event[UPN]
    )

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
2 years ago

Hi,

please check the below picture and the attached pbix file.

 

 

OFFSET function (DAX) - DAX | Microsoft Learn

 

UPN previous CC =
VAR _t =
    FILTER ( Event, Event[HMR] = EARLIER ( Event[HMR] ) )
RETURN
    MAXX (
        OFFSET (
            -1,
            _t,
            ORDERBY ( Event[dt], ASC ),
            ,
            ,
            MATCHBY ( Event[UPN], Event[HMR], Event[dt] )
        ),
        Event[UPN]
    )

 

Mythicos's avatar
Mythicos
Frequent Visitor
2 years ago

Thank you Jihwan_Kim, it works splendidly!

 

I also added a column "UPN suiv" which seeks the next event closest to Event date, by simply switching OFFSET's 1st argument from -1 to +1 and it worked, so thank you for that!