Calculating Running Total of Inventory based on store open date.

[Whitewater100]

Hello Fabric Community:

I had received some help from Ibeddlin on some RT and last stock day calculations in this file. What I am hoping to get is a table/matric that shows each store, thier products and current inventory with the amount remaining onhand. The tricky part is I'm hoping not to scroll thru all the dates inbetween. My page one is an example of the results I'm looking to get. Thank you Idedblin and all others https://drive.google.com/file/d/1uq5bLdv5kXkjz3cQ07G8iKNJ0TGkHDOH/view?usp=sharing for your help!

[Whitewater100]

Good Day Bof!

Thank you for providing this measure. I think it will be great, but have one question. If several stores have the same open date, will the measure still work or would that require a revision? 

Thank you for your reply!!

[Anonymous]

Hi @Whitewater100 ,

I believe the formula will still be effective, as it is sorted by Loc_ID.

Best Regards,

Bof

[Anonymous · Accepted solution]

Hi @Whitewater100 ,

I’ve made a test for you reference:

1\Create a new measure

Bal_On_Hand = sum(WHINV[Qty_On_Hand])-CALCULATE(

    SUM('Orders'[Qty]),REMOVEFILTERS('Orders'),

    Orders[Loc_ID]<=Max(Store_Location[Loc_ID]) && Orders[Prod_ID]=max(Products[Prod_ID])

)

2\Add a table

Best Regards

Bof