Date from Previous Row into new column

[VOx15]

Hello all, 

I have been trying to figure this out for a while and would appreciate any insight. 

My current data only has the Entry Date field and I am trying to create a new ExitDate field by grabbing the date in the next queue as the exit date for the current queue for each account. Below is a screenshow of what the ExitDate column should look like. 

Below is a portion of what is in my calculated column but this returns the max entry date in the ExitDate field for all queues. 

IF([Queue] = "Queue1", MAXX(FILTER(Table, [Account]=EARLIER([Account])), [Entry_Date_Time]),...

[fbj1973]

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!

[VOx15]

@Ashish_Mathur  here is some sample results. The exit date column is my desired result. The only issue with your formula is that when the Entry data repeats is shows blank even though there is a next queue (like below). Keep in mind this is very simplified. My actual queue name are different.

Account Number	Current Queue	Entry Date	Exit Date	Results w Dax
Account 1	Queue 1	10/13/2020	10/23/2020	10/23/2020
Account 1	Queue 2	10/23/2020	10/23/2020	10/23/2020
Account 1	Queue 3	11/16/2020	11/21/2020	11/21/2020
Account 1	Queue 4	11/21/2020	11/23/2020	11/23/2020
Account 1	Queue 5	11/23/2020	11/23/2020	blank
Account 1	Queue 6	11/23/2020		blank

[Ashish_Mathur · Accepted solution]

Hi,

My formula returns the correct result.  See the screenshot

[v-yuaj-msft]

Hi @VOx15 ，

Based on your description, you can do some steps as follows.

1. Create a caluclated column to mark the number of queue.

Column = RIGHT('Table'[Current Queue],1)

   2.  Change the format type of the column from “text” to “Whole number”.

  3.Create a measure.

Exit_Date_measure = MAXX(FILTER(ALL('Table'),[Account Number]=SELECTEDVALUE('Table'[Account Number])&&[Column]=SELECTEDVALUE('Table'[Column])+1),[Entry_Date])

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VOx15]

@Ashish_Mathur  thank you that helped for most row.

But what about the rows where the entry date is the same for two consecutive queues. With the formula you provided if the entry data is the same for two queue the exit date is blank...how can i adjust the formula?

[Ashish_Mathur]

Hi,

Please share an example and let me know the row in which my formula is not working.  Share data here in a format that i can paste in an MS Excel file - do not paste a picture.

[Ashish_Mathur]

Hi,

Try this calculated column formula

=calculate(min(data[entry_date]),filter(data,data[account number]=earlier(data[account number])&&data[Current queue]>earlier(data[current queue])))

Hope this helps.