Second Max Date based on the current Row Date

[fabric-excel]

Hello,

I'm current using M Language from Power Query to achieve this result (I developed already this code with DAX for Power BI and also with Excel formulas, but I need to create that logic with Power Query this time). The idea is, I need to get always the second max date based on the current row date.

Here below I show an example from what I'm expecting to get. I already have the date column and the idea is to create an "Previous" column like this.

Thanks in advance for all help.

[rohit1991]

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}

[v-ssriganesh]

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.

[dufoq3 · Accepted solution]

Hi @fabric-excel, check this:

Output

let
    Source = Table.FromList({#date(2020,3,1), #date(2020,3,7), #date(2020,3,5), #date(2020,10,8), #date(2019,11,30), #date(2020,3,1)}, Splitter.SplitByNothing(), type table[Date=date]),
    Dates = List.Buffer(List.Sort(Source[Date])),
    StepBack = Source,
    Ad_Previous = Table.AddColumn(StepBack, "Previous", each try Dates{List.PositionOf(Dates, [Date])-1} otherwise null, type date)
in
    Ad_Previous