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.
Hi Anonymous ,
You can try to create a measure like this to get the result:
Return Result =
VAR merge =
ADDCOLUMNS (
FILTER (
NATURALINNERJOIN (
SELECTCOLUMNS (
'Table1',
"Transaction #.", 'Table1'[Transaction #.],
"Part#", "" & 'Table1'[Part#],
"Invoice Date.", 'Table1'[Invoice Date.]
),
SELECTCOLUMNS (
'Table2',
"Part#", 'Table2'[Part#] & "",
"Changed Date.", 'Table2'[Change Date],
"Change Amount", 'Table2'[Change Amount]
)
),
[Changed Date.] < [Invoice Date.]
),
"DIFF", DATEDIFF ( [Changed Date.], [Invoice Date.], DAY )
)
RETURN
MAXX (
FILTER (
merge,
[Transaction #.]
IN DISTINCT ( 'Table1'[Transaction #.] )
&& [DIFF]
= MINX (
FILTER (
merge,
[Transaction #.]
IN DISTINCT ( 'Table1'[Transaction #.] )
&& [Part#] IN DISTINCT ( 'Table1'[Part#] )
),
[DIFF]
)
),
[Change Amount]
)
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
THANK YOU, THANK YOU, THANK YOU. This worked perfectly...