How to calculate future inventory based on production rate and demand

[Aurora-BI]

Hi Everyone, 

Like the title says I am having some issues calculating future inventory levels (End Stock) for a part my company sells. 

As of today, we have 724 units (QTYOH_06) and have a set value for how many units we produce in a week, 320. This value will eventually change, but we want to use a flat 320 for now. I have tried to filter my calculation so it will only subtract the quantities due of this specific part.

On Each "Ship Date", the related quantity will be subtracted from the total. I would like to put the results in a table, so I can show when in the future we will run into a negative inventory quantity for this part. The current measure I am trying to use is: 

End Stock =

    CALCULATE(

        SUM(Part_Stock[QTYOH_06]) + SUM('Weekly Quantities 2024'[Max Qty]) - CALCULATE(SUM('Combined Table'[Quantity]), 'Combined Table'[Part Number] = "171-CT1-1501"),

        Filter(

            ALL('Combined Table'),

            'Combined Table'[Ship Date] = MAX('Combined Table'[Ship Date])))

But this is not exactly achieving what I would like it to. I also have a table that shows how many units were built each day, I am not sure if this would be easier to use than the weekly totals.

Does anyone know how I can fix this measure so I am able to calculate future inventory levels? Any help is greatly appreciated! 

[DataNinja777]

Hi @Aurora-BI 

Have you created the calendar table and Parts dimension tables in your data model?   You can built your required output by the following logic: 

Opening stock quantity + Production quantity - Scheduled shipping quantity = Closing stock quantity

You can create the cumulative sum of production quantity and shipping quantity during the period of opening and closing dates. I tried to convert your screen print sample data, but power query "From picture" didn't convert your picture above accurately, so I'd greatly appreciate it if you could paste your data in copy & pastable format for further demonstration.  

[Aurora-BI]

@DataNinja777 

Do you know how I can provide the data in a pasteable format? I tried to post an excel file with the data but it says the file type is not supported. I think there may be too much data to directly paste the data tables in the reply on here.