Sum up columns based on date

[bstark1287]

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

[indkitty]

Hi bstark,

I think required qty is missing production schedule.

[bstark1287]

@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!

[indkitty]

@bstark1287 Not a problem. Have you built Power BI Dashboard (i.e Pbix) file. If yes, can you share through Google drive/One Drive?

[bstark1287]

I am sorry but I am unable to share due to security settings. I tried just copying and pasting into a dummy powerbi file to attach here but I am not able. 

[indkitty]

You cannot add here. Share it with Google Drive/One Drive.

[bstark1287]

@indkitty I was able to upload it here to my onedrive https://1drv.ms/u/s!ApMyUgRWG_6Cjjd7xDi_TJEZgMSF?e=NZPNbb

[indkitty]

Hi bstark,

I have built using the data posted. 

for motor stock runouts on 31/07/2022  (partial competion of orders).

for alternator 

starting inventory itself is 0, it can only run on 21/07/2022 with remaining qty. then following orders are blank.

Let me know if this is what you wanted.

[bstark1287]

@indkitty This is really close to what I am looking for! Can you share how you got these results? Basically I would like to have a slicer for the date so if I select say 7/31/22, it would show I have a negative quantity of 5 alternators which will affect job # 67890. Then if I change my slicer to 9/1/22 it would show alternators with a negative quantity of 13. I think if you share how you got this far I could possibly take it the rest of the way. What you have so far is awesome!

[indkitty · Accepted solution]

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.

[v-easonf-msft]

Hi, @bstark1287 

Can you share your expected results for further research?

Best Regards,
Community Support Team _ Eason

[bstark1287]

@v-easonf-msft I would like something very similar to the attached below. I have a slicer for the date which affects a custom measure (unrestricted stock - production schedule demand + open requisition orders). I would like to add a drill through to the part # that would show the production orders creating the cumulative total.