Forum Discussion

andresVISA's avatar
andresVISA
Regular Visitor
4 years ago
Solved

On hand Inventory Shipments (FIFO)

Hello, This is my first post so I want to thank in advance for any help received. What I'm trying to accomplish is to display the separate shipments that make up current inventory levels by product ...
Need Help
  • v-jianboli-msft's avatar
    v-jianboli-msft
    4 years ago

    Hi andresVISA ,

     

    Please try:

    Measure =
VAR _a =
    ADDCOLUMNS (
        ALL ( 'Shipments' ),
        "sum",
            SUMX (
                FILTER (
                    ALL ( 'Shipments' ),
                    [ItemCode] = EARLIER ( Shipments[ItemCode] )
                        && [Itemname] = EARLIER ( Shipments[Itemname] )
                        && VALUE (
                            RIGHT ( [Shipment date (Qtr)], 2 )
                                & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 )
                        )
                            >= VALUE (
                                RIGHT ( EARLIER ( [Shipment date (Qtr)] ), 2 )
                                    & RIGHT ( LEFT ( EARLIER ( [Shipment date (Qtr)] ), 2 ), 1 )
                            )
                ),
                [Qty]
            )
    )
VAR _b =
    MAXX (
        FILTER ( _a, [Shipment date (Qtr)] = MAX ( 'Shipments'[Shipment date (Qtr)] ) ),
        [sum]
    )
VAR _c =
    MAXX (
        FILTER (
            'Inventory',
            [ItemCode] = MAX ( 'Shipments'[ItemCode] )
                && [Itemname] = MAX ( 'Shipments'[Itemname] )
        ),
        [ On hand ]
    )
VAR _d =
    MINX (
        FILTER (
            _a,
            VALUE (
                RIGHT ( [Shipment date (Qtr)], 2 )
                    & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 )
            )
                = MINX (
                    FILTER (
                        _a,
                        [ItemCode] = MAX ( 'Shipments'[ItemCode] )
                            && [Itemname] = MAX ( 'Shipments'[Itemname] )
                            && [sum] <= _c
                    ),
                    VALUE (
                        RIGHT ( [Shipment date (Qtr)], 2 )
                            & RIGHT ( LEFT ( [Shipment date (Qtr)], 2 ), 1 )
                    )
                )
        ),
        [Shipment date (Qtr)]
    )
RETURN
    SWITCH (
        TRUE (),
        _c >= _b
            && MAX ( 'Shipments'[Shipment date (Qtr)] ) <> _d, MAX ( 'Shipments'[Qty] ),
        _c >= _b
            && MAX ( 'Shipments'[Shipment date (Qtr)] ) = _d,
            MAX ( 'Shipments'[Qty] ) + _c - _b,
        BLANK ()
    )

    Final output:

    Best Regards,

    Jianbo Li

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

andresVISA's avatar
andresVISA
Regular Visitor
4 years ago

Sounds good, here are the reference tables for my example simulating the situation:

 

"Inventory" table:

ItemCodeItemname On hand 
16431A   5,852,058

 

"Shipments Table"

ItemCodeItemnameUOMShipment date (Qtr)Qty
16431ALBQ3/20220,460
16431ALBQ1/212,469,152
16431ALBQ2/211,499,128
16431ALBQ3/21925,932
16431ALBQ4/212,116,416
16431ALBQ1/222,072,324
16431ALBQ2/223,747,820

And this is the table of the expected result:

ItemCodeItemnameUOMQuarterQty
16431ALBQ1/222,104,238
16431ALBQ2/223,747,820

 

The logic to get to the result  would be to add up Shipment quantities starting from the latest shipment until you "cover" exactly the On Hand quantity of the respective item, then only display the shipments involved in the results table.

 

Hope this helps, please don't hesitate if you need more data.