cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Objekt
Frequent Visitor

BOM quantity changing over time

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!

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Objekt 

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.

1.png

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.

View solution in original post

3 REPLIES 3
RicoZhou
Community Support
Community Support

Hi @Objekt 

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

Objekt
Frequent Visitor

Hi @RicoZhou ,

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! 

RicoZhou
Community Support
Community Support

Hi @Objekt 

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.

1.png

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.

View solution in original post

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!