Second Max Date based on the current Row Date

Hi fabric-excel,

In addition to the helpful response provided by dufoq3, could you please confirm if your query have been resolved? If so, please accept it as a solution and leave a "Kudos" so other members can find it more easily.

Thank you.

 

Hi fabric-excel ,

 

This comes up a lot in Power Query when you need the "previous" or "second max" date for each row.

You actually have a couple of good options:

 

1. The quick way: Using List.PositionOf in a sorted date list: If you just need the previous date in a column, this is super concise and efficient:

let
    Source = YourTableName,
    // Make sure your date column is type date!
    Dates = List.Sort(Source[Date]),
    AddPrevious = Table.AddColumn(
        Source,
        "Previous",
        each try Dates{List.PositionOf(Dates, [Date]) - 1} otherwise null,
        type date
    )
in
    AddPrevious

 

2. Flexible way: Filtering and using List.Max If you ever need more filtering (like by group, or other conditions), you can go with this:

let
    Source = YourTableName,
    Sorted = Table.Sort(Source, {{"Date", Order.Ascending}}),
    AddPrevious = Table.AddColumn(
        Sorted,
        "Previous",
        (current) =>
            let
                earlierDates = List.Select(Sorted[Date], (d) => d < current[Date]),
                prevDate = if List.Count(earlierDates) = 0 then null else List.Max(earlierDates)
            in
                prevDate,
        type nullable date
    )
in
    AddPrevious

 

Pro tip: If you want the “nth previous” date, just tweak the index: Dates{List.PositionOf(Dates, [Date]) - n}