Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculating remaining on-hand inventory (as reverse running total) based on ship date & item

I would like to calculate the Remaining onhand Inventory Column based on Shipdate & Item.  Can someone help me here?   Shipdate Item Qty Remaining onHand 10/11/2018 A 20 80 10/12/201...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous,

     

    You can use below calculated column formula to calculate remain onhand qty based on current item and ship date:

    Remain OnHand =
VAR _rollingQty =
    CALCULATE (
        SUM ( 'Ship Table'[Qty] ),
        FILTER (
            ALL ( 'Ship Table' ),
            [Item] = EARLIER ( 'Ship Table'[Item] )
                && 'Ship Table'[Shipdate] <= EARLIER ( 'Ship Table'[Shipdate] )
        )
    )
VAR _onHand =
    LOOKUPVALUE ( OnHand[OnHand], OnHand[Item], 'Ship Table'[Item] )
RETURN
    _onHand - _rollingQty

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hey, you also try this Measure:

 

Remain onHand =
VAR OnHand = LOOKUPVALUE(OnHand[OnHand],OnHand[Item],SELECTEDVALUE(ShipTable[Item]))
VAR RllQty = CALCULATE(SUM(fShipTable[Qty]),
                         FILTER(ALL(ShipTable),
                                 ShipTable[Item]<=MAX(ShipTable[Item])
                                   && 
                                 ShipTable[Shipdate] <= MAX(ShipTable[Shipdate])
                             )
                           )
RETURN
OnHand - RllQty