Get chronologically next entry in a table

Question

I have a table containing data with events, which are related to customers and show a chronological sequence. The relevant fields are EventID (unique key), CustomerID (unique for each customer), EventType and EventDate.

I'd now like to add a calculated column which would display if a certain event occured past another event. Say, if after an event of the type "Purchase" there was also an event of the type "Payment Received" for the same customerID happening after the first event (according to EventDate).

In Excel I'd now set up a vlookup but as far as I know there is no such functionality in PowerPivot. I've also tried splitting the tables to get the relevant event via RELATED() up but since the resulting relationship is many-to-many I get an error from PowerPivot.

Is there a way to do this in PowerPivot? (I'd also be able to manipulate the data in Power Query beforehand if that could solve the problem.)

EDIT: I have found a solution to my Problem involving SQL. I'd still be interested in a solution directly in PowerPivot, though, if that is even possible.

Answer 1

A solution involving transforming the data as it's loaded via SQL.

SELECT TOP 1 WITH TIES ev.*
    ,results.EventType
    ,results.EventDate
  FROM CustomerEvents ev
  LEFT JOIN (SELECT CustomerID
      EventType
      EventDate
      FROM CustomerEvents
      WHERE EventType = 'Payment received' or EV_EventCode = 'Payment not received') as payments
   ON payments.CustomerID = ev.CustomerID
   AND payments.EventDate > ev.EventDate 
   WHERE EventType = 'Purchase'
   ORDER BY ROW_NUMBER() over (PARTITION BY EventID ORDER BY payments.EventDate ASC)

The Order By payments.EventDate Asc is so I get the earliest Event date after the purchase Event which then gets filtered out via TOP 1 preventing me from accidentally sorting a payment Event to a much earlier purchase than it is intended for.