Calculating Product Availability Based on Sales and Rolling Purchase Receipts

[taft3je]

I am going to start off with an apology as this may get a little long winded, but I want to be sure that I include all relevant data and information.  I have been working on this last little bit, and I cannot find a technique that works.  The attached sample pbix is for four items.  We have over 10,000 items in our database.  I am using these items as they have both a high use and high purchase rates.

 We are working on a report that shows rather or not an item is available for allocation on a date that is requested by a customer or shows the earliest possible date an order / orders can be allocated and shipped.  The First Available in the top table is the first time that item is available for shipment.  The first available under the Item Number Search is the earliest time that the whole order or all orders can ship.  It will be the max of the first availables in the Inventory Details table as it will account for all of the different items on an order.

Here is a mockup of ideal state:

Then, on the second page of the report, management as requested a GL style matrix which shows all the available inventory by item and date.  In my head, it would look something like the mockup below.  It would begin on today’s date, and it would extend one full calendar year.  Sorry for the napkin sketch.

Any help I can get is greatly appreciated.  I am stuck, and I cannot figure this out.  Please let me know what additional information is helpful.  I am sure I missed something important.  I am going to try and post the file too.

[taft3je]

How do you share a pbix?

[Greg_Deckler]

@taft3je Put it up on OneDrive or other file sharing service and share a public link.

[taft3je]

Perfect.  Here is the link.  

https://1drv.ms/u/s!ApDBtHTpBH8ghe8-BaDyNIPKJjzEKQ?e=RBZxa1

Thank you.

Jim