Create a Max date column calculated over a group by while it being lesser than another date column

HI vedantsri,

You can try to use the following M query formula to create a new column with custom function to loop table records with condition:

    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            let
                 email = [Email], cDate = [Candidate Creation Date], tb =Table.SelectRows(#"Changed Type", each [Email] = email)
            in
                let
                    filtered = Table.SelectRows(tb, each [Platform Access Date] < cDate),
                    result =
                        if Table.RowCount(filtered) > 0 then
                            List.Max(filtered[Platform Access Date])
                        else
                            List.Max(tb[Platform Access Date])
                in
                    result
    )

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxKdkjPTczM0UvOz1XSUTLWN9U3MjAyBjKBUkDS0wUsagIRjdUhXos5CVqcncCiFqRrsURoqaisQtFiBnOCDkgKpsVM39CABD2ObmBRM4SWktIyFC0mCJcBpWD+N9E3JF2LMalaTOEREwsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Email = _t, #"Candidate Creation Date" = _t, #"Candidate name" = _t, #"Platform id" = _t, #"Platform Access Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Email", type text}, {"Candidate Creation Date", type date}, {"Candidate name", type text}, {"Platform id", type text}, {"Platform Access Date", type date}}),
    #"Added Custom" = Table.AddColumn(
        #"Changed Type",
        "Custom",
        each
            let
                email = [Email] , cDate = [Candidate Creation Date], tb =Table.SelectRows(#"Changed Type", each [Email] = email)
            in
                let
                    filtered = Table.SelectRows(tb, each [Platform Access Date] < cDate),
                    result =
                        if Table.RowCount(filtered) > 0 then
                            List.Max(filtered[Platform Access Date])
                        else
                            List.Max(tb[Platform Access Date])
                in
                    result
    )
in
    #"Added Custom"

BTW, I think Dax expression should more suitable for these type of calculations. If you create a calculated column in data mode table, they will simply than M query formulas and spend less resource.

Regards,

Xiaoxin Sheng