Forum Discussion

Whitewater100's avatar
Whitewater100
Icon for Solution Sage rankSolution Sage
2 years ago
Solved

Calculating Running Total of Inventory based on store open date.

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!

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Whitewater100 ,

       

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

       

      Best Regards,

      Bof