Return / Lookup next date in a column of Dates

[Tdl1982]

Hi all, 

I am new to Power BI and I am trying to create a new column based on date information in another (in Power Query). 

I effectively want my next column to display the NEXT date in the column it is referencing. If there is no next date then return TODAYs date. 

Current: 

Goal output:

I have been playing aroud with Dates and List.Max and List.Remove but not getting the output I am looking for. 

Any help is greatly appreciated. 

Thank you.

[Syndicate_Admin]

Hi @Tdl1982 

You are only looking at dates regardless of location, here is one way. Please provide sample data in a format which people can copy in the future

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lR6VYnWglQ1MMISNLJCEnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Paid = _t, Where = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Paid", type date}, {"Where", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[Paid]{[Index]+1} otherwise Date.From( DateTime.LocalNow()))
in
    #"Added Custom"

[Tdl1982]

Hello Vera_33, Thank you for this. Since I posted I worked out using two index tables and then merging together. Your way is even more efficient again. Thank you! 

[Vera_33 · Accepted solution]

Hi @Tdl1982 

You are only looking at dates regardless of location, here is one way. Please provide sample data in a format which people can copy in the future

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA0NLJR0lR6VYnWglQ1MMISNLJCEnpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Paid = _t, Where = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Paid", type date}, {"Where", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try #"Added Index"[Paid]{[Index]+1} otherwise Date.From( DateTime.LocalNow()))
in
    #"Added Custom"