BOM analysis question

Power BI Data Model
1

In my line of work in cost accounting, there is a lot of analysis where I need to compare the BOM standard to the actual consumption of components compared to units produced. I have spent hours scouring the web, forums, and videos, and I have yet to find anything satisfactory. Has Enterprise DNA thought about tackling this issue in some videos or a course? There have to be tons of people with same issue.

I’d love to help with this to share data or examples to help make it happen.
Ben

2

By BOM do you mean beginning of month?

If so, this is actual super simple in Power BI by using the OPENINGBALANCEMONTH, STARTOFMONTH functions

Check out this tutorial from the updated Mastering DAX Calculations course.

Sam

3

Ah I’ve been informed you might mean Bill of Materials.

If you want to be more specific around what this looks like then I’m sure there is content that can help on it.

Thanks

4

Yes, bills of material. There are several situations where there is really a need for content.

  1. There is a BOM and someone wants to link that to the sales table to estimate the quantity of each component required to meet that demand.
  2. Comparing production data (actuals) vs budget vs PY to assess performance. PY is the most challenging one for several reasons. I’ve been working on this with November and I’ve submitted to the forum on this and there is still no solution. Probably a good idea to get on a call to talk through each of the challenges and requirements.
  3. Actual quantity of components used at the actual price. Compare a BOM (standard quantity and cost) to the actual quantity used and the last price paid. Last price paid is particurly tricky as there is not a value each month. Need to return the most recent month’s value.
  4. Use the BOM to simulate changes in the FG based on changes in component pricing. If milk or cream change by $.20, how does that affect that cost of our FG? Keep in mind that there can be several layers of production to go from RM, to Semi-FG, to FG.

There are more but this is a good list to get started.