Forum Discussion
Sum up columns based on date
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.
indkitty I was able to upload it here to my onedrive https://1drv.ms/u/s!ApMyUgRWG_6Cjjd7xDi_TJEZgMSF?e=NZPNbb
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.
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.
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!