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

[sathiya74]

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/2018	A	30	50
10/13/2018	B	10	40
10/14/2018	A	10	40

Item	OnHand
A	100
B	50

Thank you

Sathiya

[Anonymous]

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

[v-shex-msft · Accepted solution]

Hi @sathiya74,

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

[edmar_machado]

Hi I have a similar needs, but In my case my stock called "f_saldo_estoque" and sales called "f_dados", doesnt have a direct relationship I use a table called "d_item" there is a products.

the column cod is a key between f_saldo_estoque and d_item the same happen in f_dados and d_item.

I tried to reproduce the same measure but unfortunately It didn't work.

for example: 

product ID 340693 there are 3 date of range by sales 25, 26 and 27/10 but the stock start in 26/10 with 15, so in 26/10 sold 8 i would to see in other column the difference 15-8 = 7, after that the stock increase next day plus 158, in this case happened another sales with value 1 in this case i would like to running the previous value 7+158 = 165, and subtract with 1 product sold so 165-1 = 164 then last value of this product in stock in that date.

Does anyone can help me? thanks a lot.

[skanord]

How would you write this as a measure, instead of a calculated column?

[sathiya74]

Thank you very much!  Works like a Charm!