Hello,
I am trying to create a running total of On Hand inventory by doing some Math in PowerBI. I’d like to take the existing quantity on hand and include quantity ordered for the orders for each inventory item. As the running total is calculated I’d like to use the most recently calculated value. See the screen shot for a better explanation of what I am looking for.
WarehouseAvailabletoShip.pbix (712.7 KB)
Hi @Preston
Here we need to create index first, but the ItemCode 10-001SMPL has two records on Feb 24th. Let us know based on which column can we create index.
Hi @Preston, did the response provided by @Rajesh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Rajesh,
Thanks for the response. What I need is the index by Item-SalesOrder. Attached is an updated PowerBI file that you can use. Use the Item-SalesOrder column highlighted below. Thanks for your help!
Hi @Preston
First two records Item number is same, I can’t create different index for these rows using DAX.
Can you please created index in Power Query and share the file again.
What I need is distinct index for all the rows .
Ok I am sorry. I have made the needed updates and attached a new file. Let me know if you need anything else.
Hi Preston,
Just only a question from curiosity: is the OnHand figure in the sales details not fed by new/recurring purchases ?
kind regards, deltaselect
This worked perfect! Thanks!
This is a great question, they want this to project how many orders they can fill with the existing inventory they have on hand. The ERP software shows on hand totals but does not do a very good job showing open orders against the on hand inventory levels. Make sense?
Hi @Preston, did the response provided by @Rajesh and @deltaselect help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Hi @Preston, we’ve noticed that no response has been received from you since the 25th of May. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hello,
I am responding to this forum post as I originally thought the report that @Rajesh got for me was what I needed, however after some review of the data we have found some inconsistencies with the running total. I did some looking into the measures and could use some help! It appears to be something with the indexes that are causing the issue? The logic should be that the Qty Available (Cases) minus the Ordered amount should = Remaining. It does in in the first 3 examples but in the 4th example it does not…See my screen showing what I am referring too. I also attached an updated copy of the report as well.
WarehouseAvailabletoShip.pbix (803.2 KB)
@Rajesh Thanks for the quick response! This is closer…but if you scroll down on that list you will see that there are still some items where the logic doesn’t add up. See my below screen shot…
Thanks for your help!!
Onhand qty is either blank or zero for those records. You can modify the measure based on your requirement.
And also please check your Qty Available measure
Item-SalesOrder 12130 SO# -0052138 on hand qty is 195 not 155
WarehouseAvailabletoShip.pbix (804.7 KB)
@Rajesh Thanks again for the response, however I am a little confused. See the screen shot below, the on hand quantity is not blank for the highlighted item “12105”.
@deltaselect @EnterpriseDNA @Rajesh checking on this to see if you have a had a chance to look at it?
