Second highest date

[arnaudmanir]

I'm in the auto finance industry and working on a table with thousands of serial numbers, corresponding funding date and payout date (if the loan is closed), all three listed in separate columns. If for example a vehicle was funded three times, you'll have three serial number rows each showing the corresponding advance date and payout date.

I want a list showing each asset that was financed more than once with columns showing the serial number, initial funding date, initial payout date, second funding date, second payout day, etc. I managed to create columns showing the initial and latest advance dates by using'Group by' and aggregating advance dates by min and max.

But what about the dates in between? For example if the same vehicle was funded four times, how can I pull the second advance date, the third advance date (since I already have the first and fourth occurence as min and max). 

[arnaudmanir]

Thank you, this helped immensely. However it created a new problem. I now have first, second, third funding dates each in its own column which was my objective, however each one is on a different row.

How can I merge all rows into one showing the serial number, first funding date, second funding date, etc? I tried different methods to no avail. 

[ChielFaber]

You can use the group by function from the UI and select MAX on the columns you just created. This columns will either have a value or be null. Choosing MAX will select the value. Now you get the output on one line in your table

[arnaudmanir]

Thank you, it worked perfectly! 

[ChielFaber · Accepted solution]

You probably get the result your looking for when you use group by and an index column to get an index per group.

In your use case you get an index per serial number. Look at Radacad's explanation on how to do this;

https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query

After this step you can use the if function to fill the columns. For example

If index = 1 then initial‐funding date else null

Second funding date column will be

If index = 2 the initialfundingdate else null