BOM quantity changing over time

[Anonymous]

Hi All,

I previously had a query (link below) on how to link a BOM to ingredient costs in order to workout total raw material costs for a product. The solution was simply mergeing the tables which has worked fine since.

https://community.powerbi.com/t5/Desktop/Finding-Sum-of-Calculated-column/m-p/2026477#M760887

However, it's since become appartent that the BOM has changed over time for certain products. i.e. the quantity for ingredient 124 for product 1 may have changed from 35 to 25, 6 months prior.

How would I include this into the table so the historical raw material prices reflect both the cost changes of ingredients and quanity changes of the BOM? Would this require another merge or would this not work as there would be 2 date fields ( one for ingredients and Bom change)?

Thanks in advance!

[v-rzhou-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with a sample without sensitive data.

Best Regards,

Rico Zhou

[Anonymous]

Hi @v-rzhou-msft ,

Apologies for the late reply, I had been locked out of my account. The different BOM quantity amounts were in the same rows as the current BOM quantity amounts. To get around this I made a new colum called BOM  Active which would return "1" if the price of the ingredient fitted into the time period the BOM ingredient active. I then enabled this column as a filter which worked. Thank you for your help! 

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

Do you mean that your old data will change and you want to show the old data and new data at the same time to make a comparison? If you want to use merge to solve your problem, I suggest you to add a new table to show update data and merge them to show result you want.

Here is my Sample.

If this reply still couldn't help you solve your problem, please share a sample with me. And you can show me a screenshot of the result you want. This will make it easier for me to understand your requirement.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.