Forum Discussion
Sum up columns based on date
I have 3 tables of data below. "Current Inventory", "Open requisition orders" to resupply inventory, and "Production Schedule Demand". I am wanting to calculate at what point we will run out of inventory based on the current information.
| Current Inventory | |
| Part # | Unrestricted stock |
| Motor | 1 |
| Alternator | 0 |
| Pulley | 5 |
| Panel | 16 |
| Open requisition orders | ||
| Part # | Delivery date | Quantity |
| Motor | 6/22/2022 | 5 |
| Alternator | 7/21/2022 | 3 |
| Pulley | 7/21/2022 | 10 |
| Panel | 7/21/2022 | 1 |
| Motor | 8/24/2022 | 5 |
| Pulley | 9/1/2022 | 10 |
| Panel | 9/1/2022 | 5 |
| Production Schedule demand | |||||
| Part # | Job # | Required Quantity | Required date | ||
| Motor | 12345 | 1 | 7/1/2022 | ||
| Alternator | 12345 | 2 | 7/1/2022 | ||
| Pulley | 12345 | 6 | 7/1/2022 | ||
| Panel | 12345 | 2 | 7/1/2022 | ||
| Motor | 67890 | 3 | 7/30/2022 | ||
| Alternator | 67890 | 6 | 7/30/2022 | ||
| Pulley | 67890 | 6 | 7/30/2022 | ||
| Panel | 67890 | 2 | 7/30/2022 | ||
| Motor | 98765 | 3 | 7/31/2022 | ||
| Alternator | 98765 | 6 | 7/31/2022 | ||
| Pulley | 98765 | 6 | 7/31/2022 | ||
| Panel | 98765 | 2 | 7/31/2022 | ||
| Motor | 43210 | 1 | 9/1/2022 | ||
| Alternator | 43210 | 2 | 9/1/2022 | ||
| Pulley | 43210 | 6 | 9/1/2022 | ||
| Panel | 43210 | 2 | 9/1/2022 |
Hi @bstrak1287
Refer to below as the value displayed -13 for alternator.
Logic used
current inventory (add a date column which can be 1 day less (Minimum value) than your purchase order/sales order).
Make one table with all data included as below.
current inv - Inital stock.
purchase order - Incoming qty
Work/sales order - consumption.
Create a data table 1 day earlier (as initial stock) and last date as maximum of (work order / purchase order).
You can download the file from below and work around.
https://drive.google.com/file/d/1Bl5qSBtGrnHRhEgN9Sn1kfgQp1gHHFuV/view?usp=sharing
Refer to Enterprise DNA, SQLBI for Inventory dashboard.
Let me if this solution is accepted.
11 Replies
Hi bstark,
I think required qty is missing production schedule.
indkitty apologies it was there, it was just formatted in a way it was tough for me to even discern. I have added columns in between other values to make it easier to read. Thank you for letting me know!
bstark1287 Not a problem. Have you built Power BI Dashboard (i.e Pbix) file. If yes, can you share through Google drive/One Drive?
