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.
Wow Alexis, I think that this one might be over my head. I did try and merge the two tables together, but each table contain more than 50 colums of data that I am using within the visuals and when I merge them the visuals get squirrely. I was hoping that ther was a simple way of adding a measure that filters on table 1 part and date and then reaches out to table 2 to back and find the closest before date to return the proper cost. I'll keep on it. Thank you.
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.