data table measure - add date criteria

[richardbreakey]

Hi
I have a data table with a measure to get a goods cost price.
Essentially
=sumx('All Sales Amazon','All Sales Amazon'[Quantity]*RELATED('UL Unit Costs'[Post Avg Cost]))

The tables are related by a product code.
My UL Unit Costs Table currently has only one price per code.

I would like my costs table to have a historic record of the cost price per code (i.e several dates with same code and differing cost prices)
and would then like to adjust my measure to find the nearest cost price to a specific date.

I am not sure if this is possible.

Sample data
date code cost
02/12/2024 00:00 S16 3.7
20/11/2024 00:00 95 0.21
02/11/2024 00:00 S16 3.2
02/11/2024 00:00 95 0.19

So pick a cost depending on the date as well as the related code.

Apologies if this is a convoluted explanation

Perhaps I am not thinking through correctly

Any help much appreciated
Thanks

[TMS]

Please update your profile with your location and your version of Excel.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.