Forum Discussion
Compare closest date between 2 tables
What I suggested only adds two columns to Table1 and one of them (the table-valued column) can be removed after the new custom column has been defined. Create a new blank query and paste the M code I provided over the existing code in the Advanced editor to examine the applied steps to understand more easily.
You can also do this purely in DAX without doing any table merges but you asked in the Power Query forum so I have a Power Query answer. You can also add the custom column in Power Query without doing a merge first but I think it might be too slow given your millions of rows.
I'd probably merge Table2 onto Table1 matching on Part# to get the relevant rows and then filter those rows to the ones before the invoice and then take the maximal change date row from the remaining filtered rows.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUXIEYiN9QxN9IwMjI6VYnWglI7ioEVQwFgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Transaction #" = _t, #"Part#" = _t, #"Invoice Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Transaction #", Int64.Type}, {"Part#", type text}, {"Invoice Date", type date}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Part#"}, Table2, {"Part#"}, "Table2", JoinKind.LeftOuter),
#"Added Custom" = Table.AddColumn(#"Merged Queries", "Return Result",
(r) => Table.Max(
Table.SelectRows(
r[Table2],
each [Change Date] <= r[Invoice Date]
),
"Change Date"
)[Change Amount],
type number)
in
#"Added Custom"
Thank you for that. Ya, I was looking to do this strickly with DAX. I'm new to this forum and also to PowerBI and I apologize for tagging this as a Power Querry issue.