Forum Discussion

Matt78's avatar
Matt78
Icon for Helper II rankHelper II
3 years ago
Solved

Remaining warehouse quantities after shipments

Hello

 

I have a problem getting warehouse quantities after sales shipments. I try to create a matrix where I have an item, current quantity, forthcoming shipment dates, how many will be shipped and how many will be left after the shipment..

 

I would need some advice how to get remaining qty correclty, it should  give the result as seen on the 2nd pic from Excel.

 

2912pcs is the current quantity in warehouse

How can I calculate what is the remaining qty after each shipment?

 

 

 

Thank you in advance

BR
Matt

 

 

Need Help
Tips and Tricks
  • pratyashasamal's avatar
    pratyashasamal
    3 years ago

    Hi Matt78 ,
    First create 2 measure ,

    Order Qty running total in Date =
    CALCULATE(
        SUM('Table (4)'[Order Qty]),
        FILTER(
            ALLSELECTED('Table (4)'[Date]),
            ISONORAFTER('Table (4)'[Date], MAX('Table (4)'[Date]), DESC)
        )
    )
    Remaining Qty = CALCULATE( SUM('Table (4)'[Warehouse Qty]) - [Order Qty running total in Date] )
    Now after adding this Remaining Qty you will able to see the desired result .

    Thanks ,
    Pratyasha

    If this post helps, please Accept it as the solution to help other members find it.

2 Replies

  • pratyashasamal's avatar
    pratyashasamal
    Icon for Memorable Member rankMemorable Member
    3 years ago

    Hi Matt78 ,
    First create 2 measure ,

    Order Qty running total in Date =
    CALCULATE(
        SUM('Table (4)'[Order Qty]),
        FILTER(
            ALLSELECTED('Table (4)'[Date]),
            ISONORAFTER('Table (4)'[Date], MAX('Table (4)'[Date]), DESC)
        )
    )
    Remaining Qty = CALCULATE( SUM('Table (4)'[Warehouse Qty]) - [Order Qty running total in Date] )
    Now after adding this Remaining Qty you will able to see the desired result .

    Thanks ,
    Pratyasha

    If this post helps, please Accept it as the solution to help other members find it.

  • Matt78's avatar
    Matt78
    Icon for Helper II rankHelper II
    3 years ago

    This is perfect. Huge thanks for the quick reply and solving this.

     

    -Matt