Two Column List - Descending Dates and IDs- Find Next Date in List Matching Row ID

[alcow]

Hello,

I have a table with two columns. One column contains employee IDs. The other column contains dates in descending order.

My goal is to find a formula for a third column in my table that references the employee ID for each row to look up and return the next date value (if any) in a row below that matches on the employee ID.

I am open to a solution that needs the dates in column two to be number or date format. The output column can also return number or date format. Whatever works for your awesome solution!

Thank you

[alansidman]

Copy your first query. Now join the two queries on the ID. Expand the new column and select only the dates. Group the new query on the ID and select the Max Date

Please Login or Register  to view this content.

[alcow]

Thank you for this suggestion!I really appreciate it.

Unfortunately, the workbook with the new Power Query doesn't solve my problem. I need to see each day-over-day relationship, and there are numerous Employee IDs in my real data with 3,4,5+ day-over-day relationships even within the same fiscal week.*

Your query gives me:
14 8/29/2022

I need:
14 8/27/2022 8/28/2022
14 8/28/2022 8/29/2022

--

FYI, I only had luck finding DAX solutions in the Power BI community or really complex custom column formulas.

*I have a PQ solution but I have to limited the data to just Employee IDs with 2 create dates within the same fiscal week.

[HansDouwe]

This solution only works outside the table.
Please Copy the values of the table and try in cell G4 and copy down:

Please Login or Register  to view this content.

[alcow]

Thank you--that works So, you're excluding the current row in the Match range! Smart.

Then I use

Please Login or Register  to view this content.

to find a relationship between the two dates.

If anyone is curious, I did also find a Power Query workaround based on using two index columns.* I am seeing some chatter about Index being finicky sometimes in Power Query though + the first row of data gets sacrificed and I couldn't find a workaround yet..

Excited to have multiple possible solutions.Thanks Excel people!

*I'm not allowed to post a link but you can find the Stack Overflow article using this path: questions/54827273/power-query-subtract-row-below-from-row-above-using-multiple-conditions