Calculating Balance on Hand

[Whitewater100]

Hello:

I am trying to calculate blance on hand where stores draw down inventory based on their open date and in a separate WHINV table resides the beginning inventory. I am attaching the example file and an example of expected results. The store have an orders table alhough the WHINV is in a separate table for only begining inventroy that does not reference stores. 

If possible could you also help with another measure that identifies the last date (Store Open Date) before inventory falls into the negative? 

Thank you very much. File attached..https://drive.google.com/file/d/1PIpjNZeWTU_1mYRtK6N184zmQbstr5hp/view?usp=sharing 

[Whitewater100]

Store Open Date is withdrawal Date. Thanks..

[lbendlin]

[Whitewater100]

Thank you for replying. Just as an example. One product ID 1 - it starts out with 185 units in the WHINV file. Store One orders 35 units 0n 1-10-2024 so the inventory would be at 150 on Jan 10 -2024. Then on 3-25-2024 the next store ID =2, orders 76 units leaving 74 units as of 3-25-2024. And it follows on lke that. The hard part(I'm rusty) for me was that the WJINV is not by store but the withdrawals for store openings are by store. The store order date key is impoertant as the WHINV is static and is prior to any withdrawls. I hope this helps explain. I tried to make a WHINV Date relationship but was unable to becasue the order table had that relationship and the order table is used more for determining what date a product goes out of stock. Thank you for looking at this problem! 

[lbendlin]

Yes, my bad - that should have been an inactive relationship.

So now you want to know when each product is projected to hit zero stock?

[Whitewater100]

That would be awesome. Either last instock date or firsat out of stock. Either one is great. Thank you very much!!

[lbendlin]

see my reply above

[Whitewater100]

Hi Idendlin: Thanks so much for your work on this. Would you happen to have the final file? The one I downloaded is before you revised. I wan't sure which relationship you wanted to make inactive. I tried it between WHINV & Dates but the graph does not match yours. I really appreicate your help!!!

[lbendlin · Accepted solution]

see if this is better.

[Whitewater100]

That is fantastic! I'd love to give Kudos but I don't see where. Thank you so much:-)

[lbendlin]

Is this a more appropriate data model?

What is "Store Open Date"?  Looks like all products were depleted with their first orders.