Get the next latest Date after the Max Date in Power Query

[third_hicana]

Hi. Asking for you help how can I create a custom column in power query where it will get the next latest date after the max date in a Date column. For example, in the column below the max date is 17/04/2024 then the next latest date is 27/03/2024. I want to get the 27/03/2024 in a created column. 

Thank you very much for your help.

-Third

[third_hicana]

@bhanu_gautam  Than you very much for your help.

[bhanu_gautam · Accepted solution]

@third_hicana ,Go to the Add Column tab.
Click on Custom Column.
Use the following formula to get the next latest date:

let

    // Load your data

    Source = YourDataSource,

    // Sort the date column in descending order

    SortedTable = Table.Sort(Source,{{"Date", Order.Descending}}),

    // Add an index column

    IndexedTable = Table.AddIndexColumn(SortedTable, "Index", 1, 1, Int64.Type),

    // Get the max date

    MaxDate = List.Max(IndexedTable[Date]),

    // Filter out the max date

    FilteredTable = Table.SelectRows(IndexedTable, each [Date] <> MaxDate),

    // Get the next latest date

    NextLatestDate = FilteredTable{0}[Date],

    // Add a custom column with the next latest date

    AddedCustomColumn = Table.AddColumn(IndexedTable, "NextLatestDate", each if [Date] = MaxDate then NextLatestDate else null)

in

    AddedCustomColumn