Calculation of Product cost for Sale order line from BOM with nearest purchased price of products

[zenisekd]

Hi, 

I am having a trouble with solving following scenario.
I am trying to come up with calculation of Product cost for Sale order line table (I would like to calculate a margin from it). However, it is above my capabilities. 

• The products that we sell are simple products (with a single line of BOM) and composite (with multiple BOM lines).
• One product can be sold as simple product or as a part of the composite. 
• The cost of the product (when sale is done) is supposed to be created as the cost of purchased product from Purchase order line with the nearest date of the product purchase (to the sales). In other words, we sell product A on Firday, the cost of it is taken from the nearest purchase, e.g. Thursday.
• In order to get the cost of the composite, I have to look into BOM, collect the nearest costs from purchase  (based on sell date) and sum them. And here lies the problem. 
  The model look like this at the moment: 
  
  I am trying to figure out, how to get the cost as a new column to the Sale order line table ... or perhaps to calculate it as a measure. Any ideas?
  
  The model above is not completed, I am just showing the key components for the calculation...
  
  

[lbendlin]

Try to redraw your data model.  As I understand it you have a Composites -> Products -> BOM lines  dimension hierarchy and then you have purchase order and sales order facts ?