Date from Previous Row into new column

Hi all,

I have a somewhat similar problem. Some background first.

I have three tables:

• Grade Crossing Inventory Current (GCI) – Contains the current information of each railroad grade crossing in North America.
• Grade Crossing Inventory Historical (GCI Historical) – Contains the historic information of each railroad grade crossing whenever an update to the corresponding current record is made (e.g. installed new gates, or closed, etc.). The records go back to 1970.
• Highway Rail Accident (HRA) – Contains all accident records that occurred at grade crossings. The accident records go back to 1975.

All three tables are available to link via OData at https://data.transportation.gov/browse?category=Railroads

The primary key is the “crossingid” column in the GCI Current. I created one to many relationships between the primary key in GCI and the corresponding keys in GCI Historical and HRA (there, the column is called “gradecrossingid”). The GCI and GCI historical both contain a column called “revisiondate”: the one in the current table is the latest, while the ones in the historical are the “old” revision dates that are retained whenever the current record is updated. So, one crossing id can have multiple revision dates and multiple accidents.

What I am after here is to select a record from the HRA table and select from the GCI historical table the crossing record that was in effect at the time of the accident. I tried by creating a dummy column “EndDate” in the GCI historical: this column would show me the date before the latest revision date. I used the formula "EndDate", each Date.AddDays([revisiondate], -1). All this does however is display the day before the revision date of the same record, but I want it to show me the day before the revision date of the previous record. With that column I should be able to create a lookup range called IncidentDate =let IncidentDate = [Date] in

Table.SelectRows([Grade Crossing Inventory Historical], each [revisiondate] <= IncidentDate and [endDate] >= IncidentDate). I get nothing.

Where is my mistake?

Thanks for any help or hints here!