Need assistance creating a consumption/replenishment report.

[ThayerCL]

Caveats: 1) I can’t upload the specific data I’m working with so I created a facsimile and uploaded it here: https://docs.google.com/spreadsheets/d/1nN6P7nh3sUu6UEpJ5eG5A0bn8pRR0piW/edit?usp=sharing&ouid=11456...

2) I don't have Power BI on my personal computer so I had to recreate the data facsimile in Excel.

Background: I’m attempting to create a consumption and replenishment report that I can update daily when I pull new data. All data is maintained in excel. The required output must summarize all activity by the top level build activity. I just started using Power Query/Power BI on 10Mar, so I'm not well versed in it and everything I've learned has been directional based on this project.

Available to me as three separate files:

Data set1: Parts list, quantity on hand, and quantity on order, delivery date.

Data set 2: consumption by activity: sub and main assemblies.

Data set 3: build schedule: build activity by date sub and main assemblies.

Background: I’ve used Power Query to join and transform the data sets to allow me to update the source files and get the new results. This is my first time using Power Query and I've reached my personal knowledge limit.

Original equation used (assumes all activities related to an item happen before moving to the next item): total on hand - (all quantity consumed by final build activity 1) - (all quantity consumed by final build activity 2) - etc. ------ This incorrectly displayed the data showing that

I believe the correct equation would be: total on hand for an item - (first quantity required) - (next quantity required) - (next quantity required) - etc. ------- I've created an Example of Consumption worksheet to help explain this concept to explain how time affects the quantity on hand to people I work with.

I’ve used Power Query to join and transform the data sets to allow me to update the source files and get the new results. This is my first time using Power Query and I've reached my personal knowledge limit.

Help Needed: I can't figure out how to add a running total column to the Consumption Schedule that shows by part when a part reaches zero or negative based on the starting on hand quantity in tblAvailable.

I was able to use https://goodly.co.in/running-total-power-query/ to get a conditional running total going, but I could not for the life of me figure out how to set the starting value as the total on hand from tblAvailable. I also did not understand what OutTable or InTable are but know they were critical to creating the running table in this format.

I believe there is a way to conduct the calculations based on the Consumption Schedule that I created, but I don't know it so I created the Consumption Breakout. This shows parts consumed by day, but I need a way to subtract across all the columns.

[lbendlin]

That's a tall project for starting with Power BI.  In addition to that you need to know that Power BI has no memory.  It cannot do any manipulations on existing datasets. It always reloads everything.  That means you need to do the logging and history keeping outside of Power BI.

In addition, some of your tables are in a format that Power BI does not like  (especially Consumption breakout).  You would have to unpivot them and bring them into usable shape before attempting any logic implementation. However they don't seem to be required so I didn't load them.

Here's the data model I would propose:

Did you know you have quite a few "Next Delivery Date"  values in the past?

Anyway - next you would do the material planning to see how far you can stretch the parts.  Are you assuming that item 1 has higher priority than item 2 etc ?   If not then you would need to specify the priority somewhere.

Frankly speaking - Power BI is a reporting tool. It is not the right tool for material allocation, production planning and inventory management.  That's what ERP systems are for.

[ThayerCL]

Thanks for the reply. This is just a facsimile data set of what I'm actually working with, so dates aren't representative of real dates.  The consumption breakout was a resulting table, not an input.

The real thing I'm looking to see if I can do is get a running subtractive total column added to the Consumption Schedule but I can't figure out how to a) set the initial value from tblAvailable then b) subtract the values in the Consumption Schedule from it. I've added an index column in my working file.

ex: part001: would start at 18 from tblAvailable, subtract 5 and return 13, subtract 5 and return 8, subtract 5 and return 3, subtract 5 and return -2, subtract 5 and return -7, etc. based on the way it is ordered on the consumption schedule. 

[lbendlin]

my previous opinion still stands: Power BI is a reporting tool. It is not the right tool for material allocation, production planning and inventory management.  That's what ERP systems are for.