Power BI - find value associated with closest date in DAX

I have two tables in Power BI:

and

I want to be able to be able to add Table 2 to Table 1 where the price is the closest, but earlier date from Table 2 (see below):

Creating a relationship causes problems, as it is a many-many relationship, so instead I've been trying to do it using DAX. I can create another table, but I actually want to add it as a calculated column to table 1 rather than create a new table. Currently this is the code I am using to create a new table - any ideas on how to transition this to a calculated column in table 1?

<code> _new_table = var _prices = SELECTCOLUMNS('Table2',"Item",'Table2'[Item]&"","Start Date", 'Table2'[StartDate], "Price", 'Table2'[Price])

var _sales = SELECTCOLUMNS('Table1',"Sale Date",'Table1'[SaleDate],"Item",'Table1'[Item]&"")

var _compare = ADDCOLUMNS(NATURALLEFTOUTERJOIN(_sales,_prices),"Difference",IF(DATEDIFF([StartDate],[Posting Date],DAY)<0,999999999,DATEDIFF([StartDate],[SaleDate],DAY)))

var _min_days = MINX(_compare, [Difference])

var _final = FILTER(_compare,[Difference]=_min_days || ISBLANK([Difference]))

return _final</code>

This can be performed in PowerQuery or DAX. If you are pulling from a source that allows query folding (e.g., SQL Server), I prefer solving in PowerQuery, but ultimately you can try each and see which works better with your data.

PowerQuery Solution

1. First, ensure you have a primary key on your sales table. If not, create an index column.
2. Then, perform a left-outer join (Merge Tables) of your tables on the item column.
3. Filter where the left date (sales table) is greater than or equal to the right date (pricing table).
4. Sort the table by the right date (pricing table) ascending and buffer your table (Table.Buffer()).
5. Remove duplicates based on your primary key(s) or index column.

DAX Solution

You can do essentially the same behavior in DAX as well. In this case, connect your pricing table to your product dimension table as a separate fact table.

'Sales Fact Table' <- 'Product Dimension' -> 'Pricing Fact Table'

Create the following column on the 'Sales Fact Table':

CALCULATE(
    LASTNONBLANKVALUE( 'Pricing Fact Table'[Date], 'Pricing Fact Table'[Price] ),
    FILTER(
        RELATEDTABLE( 'Pricing Fact Table' ),
        'Pricing Fact Table'[Date] <= 'Sales Fact Table'[Date]
    )
)

Here's how this works:

RELATEDTABLE(...): Narrows the pricing fact table to the relevant rows by using the product dimension relationships.

'Pricing Fact Table'[Date] <= 'Sales Fact Table'[Date]: Pricing effective at or before time of sale.

LASTNONBLANKVALUE(...): Sorts the pricing fact table by the effective date and returns the last price value (subject to the prior filtering).