Create new column based on values from 2 rows

You could use an Index column and find the date with the next index and subtract the date of the current row from the one of the next row.

For example, in this example:

- I replicated the column as a number or text (just to recreate the column - you might not need this)

- Added an index

- Converted the date to type date (as it was text or number) - in your case you could also just subtract the numbers of course, but thought this was cleaner

- Stored the maximum index number in "variable" Max

- Subtracted the current date from the date with the next index number (step Days) - note the if statement as it will cause an error if not added due to it trying to subtract a date from a value that does not exist for the last row. I made the last row value for DateToNext 0, but could be any number or null. 

- Removed the redundant "helper "columns

let Source = Table.FromRecords({
[DateText = 20240501],
[DateText = 20240502],
[DateText = 20240503],
[DateText = 20240507],
[DateText = 20240508],
[DateText = 20240509],
[DateText = 20240510],
[DateText = 20240513]
}),
Index = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
Date = Table.AddColumn(Index,"Date", each Date.From(Text.From([DateText])),type date),
Max = List.Max(Date[Index]),
Days = Table.AddColumn(Date, "DaysToNext", each if [Index] <Max then Date[DateText]{[Index]+1} - Date[DateText]{[Index]} else 0),
RemovedRedundantCols = Table.RemoveColumns(Days,{"DateText", "Index"})
in
RemovedRedundantCols

Hope this helps

Hi Anonymous 

Here is another Power Query solution for your reference. The idea is to:

1. add an index column starting from 1 and the second index column starting from 0, 

2. merge the current query to itself by two different index columns,

3. expand the merged result to get the next row date for each row, 

4. add a custom column to get the duration days between two days,

5. remove unnecessary columns. 

let 
Source = Table.FromRecords({
    [DateText = 20240501],
    [DateText = 20240502],
    [DateText = 20240503],
    [DateText = 20240507],
    [DateText = 20240508],
    [DateText = 20240509],
    [DateText = 20240510],
    [DateText = 20240513]
}),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DateText", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 0, 1, Int64.Type),
    #"Merged Queries" = Table.NestedJoin(#"Added Index1", {"Index"}, #"Added Index1", {"Index.1"}, "Added Index1", JoinKind.LeftOuter),
    #"Expanded Added Index1" = Table.ExpandTableColumn(#"Merged Queries", "Added Index1", {"DateText"}, {"DateText.1"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Added Index1", "DaysToNext", each Duration.Days(Date.From([DateText.1]) - Date.From([DateText]))),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"DateText", "DaysToNext"})
in
    #"Removed Other Columns"

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!