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.
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.
You cannot add here. Share it with Google Drive/One Drive.
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.
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!
Hi, bstark1287
Can you share your expected results for further research?
Best Regards,
Community Support Team _ Easonv-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.
