Lookup value (with multiple results) from same table and with multiple filters

[mwu1]

Hello, I have the following issue:

Context:

I currently have a report that tracks an activity log. 1 row = 1 activity. The row will tell us what is the activity (Activity ID (From)) and what is the next activity (Activity ID (To)) it triggers. Each row has a date-time (Activity Date) of when the Activity ID (From) happened. 

Objective:

For each row, I'd like to find out what is the Activity date for when Activity ID (from) = preceding Activity ID (to)

I've tried the following formula

T0 = Calculate(firstNONBLANK(Reporting[ActivityDate], 1), Filter(Reporting, Reporting[ActivityToId] = EARLIER(Reporting[ActivityId])))

but it's only retrieving the first ever date of that activity instead of the closest preceding one. I would also like to add a filter so that it only looks up dates with the same Request ID (column A).

Thank you in advance!

[mwu1]

Managed to get an answer from further researching

T0 =

VAR _last_date = Reporting[ActivityDate]

RETURN

CALCULATE (

MAX ( Reporting[ActivityDate] ) ,

ALLEXCEPT ( Reporting , Reporting[ReqIDId] ) ,

Reporting[ActivityDate] < _last_date, Reporting[ActivityToId] = EARLIER(Reporting[ActivityId]))

[v-eqin-msft]

Hi, @mwu1 

You have a great approach, thanks for sharing. You can accept your answer as a solution to help more people with similar problems.

Best Regards