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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sathiya74
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?

 

ShipdateItemQtyRemaining onHand
10/11/2018A2080
10/12/2018A3050
10/13/2018B1040
10/14/2018A10

40

 

ItemOnHand
  
A100
B50

 

Thank you

Sathiya

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
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

7.PNG

 

Regards,

Xiaoxin Sheng

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

View solution in original post

5 REPLIES 5
gutovix
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
v-shex-msft
Community Support
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

7.PNG

 

Regards,

Xiaoxin Sheng

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

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.

 

edmar_machado_2-1667048713737.png

 

 

 

edmar_machado_0-1667048531494.png

 

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

Thank you very much!  Works like a Charm!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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