Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Frequent Visitor

## 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/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

1 ACCEPTED SOLUTION
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
5 REPLIES 5
Frequent Visitor

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```
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Regular Visitor

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.

Advocate II

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

Frequent Visitor

Thank you very much!  Works like a Charm!

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors