I created a report in Excel using PowerPivot that shows how many of each part number are needed each day to fulfill customer orders. To do this I pulled 2 tables from our ERP database. (The data below is simplified for this example but should convey what data is available.)
1st: The part table containing Part # and stock on hand
2nd: Customer order line table containing the following columns for only open active orders:
Order ID, Part #, open order Qty, desired ship date
The report looks like the below (though I have added conditional formatting to show when stock would be depleted.) it is a pivot table with the Part ID and SOH columns taken from the part table for rows, ship date used for columns and open order qty for values.
The issue: currently the report is only showing the parts that are actually shipped. It does not show the sub tier component demands if stock of the parent part is depleted. I would like to expand the report to include demands for sub tier components if they are needed to fulfill orders.
I can either join the assembly tree to the order table to expand out the rows for all possible component demands or bring in the assembly tree as a separate hierarchical table with Parent Part ID, part ID, and qty per parent columns.
Conditions:
Part Table
Assembly tree table
Order table
Desired report table
I have a start by having 2 calculated columns on my order table (which for my test I joined with the assembly tree to expand out). However the formulas only works for levels 0 and 1 (0 is the actual order). For the levels below 1 it overstates the needs. I keep running into circular logic when attempting to solve this because I want to determine if the parent part has been depleted on or before for the order line in question which in turn means summing all orders up to the date of this order. Calc_Qty_Open is the field I put on the value section of the pivot table.
1st: Patent Short = =min(CO_LINES[Open_Qty]/[Component_Qty],max(0,-(LOOKUPVALUE(Part[QTY_ON_HAND], Part[ID],CO_LINES[parent]) - CALCULATE(sum(CO_LINES[Open_Qty]), filter(all(CO_LINES), CO_LINES[PART_ID] = EARLIER(CO_LINES[Parent]) && (CO_LINES[ShipDate] < earlier(CO_LINES[ShipDate]) || (CO_LINES[ShipDate] = earlier(CO_LINES[ShipDate])&& CO_LINES[ID] <= EARLIER(CO_LINES[ID]))))))))
2nd: Calc_Qty_Open=if([Level]=0,[Open_Qty],[Parent_Short]*[Component_Qty])
1st: The part table containing Part # and stock on hand
2nd: Customer order line table containing the following columns for only open active orders:
Order ID, Part #, open order Qty, desired ship date
The report looks like the below (though I have added conditional formatting to show when stock would be depleted.) it is a pivot table with the Part ID and SOH columns taken from the part table for rows, ship date used for columns and open order qty for values.
| PART_ID | SOH | 1/28/22 | 1/31/22 | 2/1/22 | 2/2/22 |
| A | 12535 | 504 | 0 | 554 | 252 |
| B | 39728 | 0 | 0 | 0 | 0 |
| C | 15538 | 966 | 462 | 420 | 462 |
| D | 13060 | 150 | 250 | 300 | 250 |
| E | 4517 | 0 | 0 | 0 | 52 |
| F | 1068 | 0 | 0 | 0 | 0 |
| G | 12735 | 75 | 0 | 300 | 375 |
| H | 525 | 0 | 0 | 60 | 300 |
The issue: currently the report is only showing the parts that are actually shipped. It does not show the sub tier component demands if stock of the parent part is depleted. I would like to expand the report to include demands for sub tier components if they are needed to fulfill orders.
I can either join the assembly tree to the order table to expand out the rows for all possible component demands or bring in the assembly tree as a separate hierarchical table with Parent Part ID, part ID, and qty per parent columns.
Conditions:
- Only create demand for sub tier components for the qty that exceeds stock on hand for the parent.
- The hierarchy for assemblies can go several layers deep. There is likely stock of parent parts at each layer to be considered (from part table)
- A component may be shipped directly to customer and also included in another assembly. Both demands need to be displayed in total for the part on the date (assuming the sub component demand
- Need to perform need determination (qty shown on report)on a progressive date basis. Example, Count all direct and sub component demands for 1/28 to determine what is still available for 1/29.
- File currently used powerpivot with refresh on file open set so that when employees open the file it shows them the current status for all parts and what is needed in the next few weeks. Want solution to work with refresh on file open.
- Solution may be an addition to the SQL query pulling one or more of the tables from the ERP, a measure or calculated column. I do not want to use power query with this file as it is not as easy to set to auto refresh on file open in my experience. If power Query is the only path then I will accept that and a solution that utilizes it.
Part Table
| Part# | SOH |
| A | 20 |
| B | 10 |
| C | 15 |
| D | 30 |
| Parent | Part # | Qty_Per |
| A | B | 1 |
| A | C | 2 |
| C | D | 1 |
| Order_ID | Part# | Qty | Ship_Date |
1 | A | 15 | 2/1/2022 |
2 | A | 10 | 2/2/2022 |
3 | C | 8 | 2/1/2022 |
| Part# | SOH | 2/1/2022 | 2/2/2022 |
| A | 20 | 15 | 10 |
| B | 10 | 5 | |
| C | 15 | 8 | 10 |
| D | 30 | 3 |
I have a start by having 2 calculated columns on my order table (which for my test I joined with the assembly tree to expand out). However the formulas only works for levels 0 and 1 (0 is the actual order). For the levels below 1 it overstates the needs. I keep running into circular logic when attempting to solve this because I want to determine if the parent part has been depleted on or before for the order line in question which in turn means summing all orders up to the date of this order. Calc_Qty_Open is the field I put on the value section of the pivot table.
1st: Patent Short = =min(CO_LINES[Open_Qty]/[Component_Qty],max(0,-(LOOKUPVALUE(Part[QTY_ON_HAND], Part[ID],CO_LINES[parent]) - CALCULATE(sum(CO_LINES[Open_Qty]), filter(all(CO_LINES), CO_LINES[PART_ID] = EARLIER(CO_LINES[Parent]) && (CO_LINES[ShipDate] < earlier(CO_LINES[ShipDate]) || (CO_LINES[ShipDate] = earlier(CO_LINES[ShipDate])&& CO_LINES[ID] <= EARLIER(CO_LINES[ID]))))))))
2nd: Calc_Qty_Open=if([Level]=0,[Open_Qty],[Parent_Short]*[Component_Qty])
