Forecast BOM inventory based off Demand

DAX DAX Calculations
1

I need to forecast future need of my BOM parts based on current inventory and future demand. I have a forecast for the main parts. I have a BOM table that tells me each part that is required to build the main parts. I have current inventory of my BOM parts. I need a matrix the has the BOM parts in the rows, month/year in the columns, and my inventory levels in the values. This matrix will tell me what month I will go into a negative inventory value. I have attached a sample pbix file. Thanks for your help.

SampleFile.pbix (60.3 KB)

2

Hi @Usates,

I’ve added a Month & Year column to your Dates table and marked that as a Date table.
Give this a go.

image
image1389×451 39.6 KB

Here’s your sample file.
SampleFile.pbix (58.2 KB)

I hope this is helpful

1 Like
3

Thank you so much for your help on this. One of the issues though is BOM part EE is built by combining parts FF and GG…so there are three levels here.

It takes 10 EE’s to make Item ID 2, but EE has to be built with 20xFF’s and 10xGG’s.

I hope I’m explaining this correctly.

4

Hi @Usates,

Didn’t see that, sorry… I included 2 versions for calculating the EE need, think you want [Total Qty v2] but wasn’t completely sure, just amend to your needs. Had to add some helper columns as well.

If this is not what you where expecting please provide an XLSX with the required outcome.

Here’s your sample file:
SampleFile.pbix (62.0 KB)

I hope this is helpful

1 Like