Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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
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.
How would you write this as a measure, instead of a calculated column?
Thank you very much! Works like a Charm!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |