List.Max function @ Power Query M (Inline Subquery)

[kent-culpepper]

I am trying to Add a Column ("Max_Quote_Date") that returns the Max Date of another Column ("Quote_Date") with 2 conditions ("Investment_Key","Month_Date"), using the following expression;

#"Added Max Quote Date" = Table.AddColumn(#"Filtered Rows1", "Max_Quote_Date", each 
    List.Max(#"Filtered Rows1"[Quote_Date], 
            [Investment_Key] = #"Filtered Rows1"[Investment_Key] and [Month_Date] < #"Filtered Rows1"[Month_Date]
        )
    )

It doesnt seem to be applying both conditions. And the performance is very slow.. I am sure there is a more efficient method.

Below is a visual illustration of what i am trying to achieve;

I liken this to an "inline subquery" in terms of SQL.

Any ideas?

Thanks much for your support.

[Ahmedx]

firstly, the conditions are unclear, try to explain in words what you want and what conditions must be met
secondly, share data that will help you

[kent-culpepper]

Where 

[Investment_Key] = #"Filtered Rows1"[Investment_Key] and [Month_Date] < #"Filtered Rows1"[Month_Date]

[Ahmedx]

[Investment_Key]  this is Value
 #"Filtered Rows1"[Investment_Key]  this is List
why do you want to compare the value with the List🙄

so I ask what do you need?

[kent-culpepper]

If we reference the visual, I am indicating that I want to return (in a new column) the yellow-shaded value where it is currently null.

Which means I want to find the Max(Quote_Date) Where the Investment_Key = the Prior Step Investment Key and the Month_Date < the Prior Step Month_Date.

9/30/23 = Max(Quote_Date) Where Investment_Key = Aeva Tech And Month_Date < 10/31/23.

[Ashish_Mathur]

Hi,

If you are amenable to the idea of solving it with a calculated column DAX formula, then share some data to work with (in a format that can be pasted in an MS Excel file).