Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
zenisekd
Super User
Super User

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

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: 
    zenisekd_0-1618396685689.png

     


    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...

1 REPLY 1
lbendlin
Super User
Super User

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 ?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.