Forum Discussion
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 prec" that will indicate, for each event (row), the UPN of the previous event for the same person (i.e. same HMR) witth the date closest to the current event's date. If there's no previous event for that HMR, cell should be left blank.
So the result would look like this:
How should I create the new "UPN prec" in DAX?
Thanks!
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] )
2 Replies
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] )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!
