Lookup value from another table based on key match and nearest date match

[Coffee321]

I'm trying to bring a value from another table but there is no relationship because of duplicates. This is the Dim table, the value I'm trying to bring over is on the PerUnit column:

I'm trying to create a calculated column on the fact table that will bring the PerUnit value based on an Item match and the closest date match from the Dim table. The FactTable looks like this:

The calculated column would look like this

I've tried using lookupvalue and other measures to achieve this but haven't been able to. The dim table contains hundreds of items, converting the dim table into a proper dim table that will allow me to connect it to the fact table has been challenging ( hence the approach of a calculated column). Any help will be greatly appreciated.

[ryan_mayu · Accepted solution]

@Coffee321 

you can create a column

Column =

var _last=maxx(FILTER(Dim,'Dim'[Date]<='Fact'[Date]&&'Fact'[Item]=Dim[Item]),'Dim'[Date])

return maxx(FILTER(Dim,'Dim'[Item]='Fact'[Item]&&'Dim'[Date]=_last),Dim[PerUnit])

or create a measure

Measure =

var _last=maxx(FILTER(all(Dim),'Dim'[Date]<=max('Fact'[Date])&&Dim[Item]=max('Fact'[Item])),'Dim'[Date])

return maxx(FILTER(all(Dim),Dim[Item]=max('Fact'[Item])&&'Dim'[Date]=_last),'Dim'[PerUnit])

pls see the attachment below