Filter a merged table list to another date column

[Bi2thelly]

I'm not sure I'm doing this correctly but I merged two tables together but did not expand as I wanted records from one table tied to the main table. I'm trying to filter the records to get the max column value that is less than than a column value from the main table. I was trying to use List.Max and Table.Max but have not been able to get them to work correctly. Any insights on this would be great! What I'm looking for is the MAX GoLive_Actual that is less than the NOTI_START_DT.

List.Max([BridgHistory2][GoLive_Actual], 0,[BridgHistory2][GoLive_Actual] < [NOTI_START_DT])

[Bi2thelly]

Thank you both for the reply. Both solutions work however, I went with the one using the variables as I may need to add more critieria. Thanks again!

[Omid_Motamedise]

Hi @Bi2thelly 

use the below formula (make sure both the columns are in type date)

=List.Max(List.Select([BridgHistory2][GoLive_Actual], (x)=> x < [NOTI_START_DT]))

[v-heq-msft · Accepted solution]

Hi @Bi2thelly ,
Based on your description, you can try the following, first, filter the BridgHistory2 table to keep only the rows where GoLive_Actual is less than NOTI_START_DT. Then, find the largest GoLive_Actual date from the filtered table.

let
    BridgHistory2 = #"BridgHistory2",
    NOTI_START_DT = [NOTI_START_DT],
    FilteredTable = Table.SelectRows(BridgHistory2, each [GoLive_Actual] < NOTI_START_DT),
    MaxGoLiveActual = List.Max(FilteredTable[GoLive_Actual])
in
    MaxGoLiveActual

Best regards,
Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly