Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Inventory

I have a table of backordered items and a table of incoming shipments to fulfill those backorders. The quantity of the backorder often exceeds the quantity of any one fulfillment shipment. For a given item i need to take a cumulative of arriving shipments and calculate when I will have recieved enough to fill the backorders. Be gentle, I'm a newb.

ITEMIDActual Back Order Qty
UCJV300-16016

 

ITEMNUMBERAVAILDATEQTYREMAINING
UCJV300-1608/16/20218
UCJV300-1608/27/20216
UCJV300-1609/6/20216
UCJV300-1609/22/20212
UCJV300-1609/23/202112
UCJV300-1609/29/20216

 

I would in this example be able to fill backorders by 9/6/2021.  Any help would be appreciated.

Here is a link to the pbix https://drive.google.com/file/d/15mXmznI2b2f5kLliRVJL2GYJAqF-jUmc/view?usp=sharing

 

 

Need Help
  • DataInsights's avatar
    DataInsights
    5 years ago

    Anonymous,

     

    Try this solution.

     

    1. Create measures:

     

    Qty Remaining = SUM ( ArrivingShipments[QTYREMAINING] )

Backorder Fill Date = 
VAR vBackOrderQty = [Actual Back Order Qty]
VAR vBaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            ArrivingShipments,
            ArrivingShipments[ITEMNUMBER],
            ArrivingShipments[AVAILDATE]
        ),
        "@QtyRemaining", [Qty Remaining]
    )
VAR vFinalTable =
    ADDCOLUMNS (
        vBaseTable,
        "@RunningTotal",
            VAR vDate = ArrivingShipments[AVAILDATE]
            RETURN
                CALCULATE ( [Qty Remaining], ArrivingShipments[AVAILDATE] <= vDate )
    )
VAR vResult =
    CALCULATE (
        MIN ( ArrivingShipments[AVAILDATE] ),
        FILTER ( vFinalTable, [@RunningTotal] >= vBackOrderQty )
    )
RETURN
    vResult

     

    This measure was already in your pbix:

     

    Actual Back Order Qty = 
    SUM(BackOrderedItems[BackOrder])
        -SUM(BackOrderedItems[QtyReserved])
            -Sum(BackOrderedItems[Picked])

     

    2. In table visual "From Back Ordered Items Table", ITEMID should be from table INVENTORYMASTERTABLE.

     

     

     

2 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    Anonymous,

     

    Try this solution.

     

    1. Create measures:

     

    Qty Remaining = SUM ( ArrivingShipments[QTYREMAINING] )

Backorder Fill Date = 
VAR vBackOrderQty = [Actual Back Order Qty]
VAR vBaseTable =
    ADDCOLUMNS (
        SUMMARIZE (
            ArrivingShipments,
            ArrivingShipments[ITEMNUMBER],
            ArrivingShipments[AVAILDATE]
        ),
        "@QtyRemaining", [Qty Remaining]
    )
VAR vFinalTable =
    ADDCOLUMNS (
        vBaseTable,
        "@RunningTotal",
            VAR vDate = ArrivingShipments[AVAILDATE]
            RETURN
                CALCULATE ( [Qty Remaining], ArrivingShipments[AVAILDATE] <= vDate )
    )
VAR vResult =
    CALCULATE (
        MIN ( ArrivingShipments[AVAILDATE] ),
        FILTER ( vFinalTable, [@RunningTotal] >= vBackOrderQty )
    )
RETURN
    vResult

     

    This measure was already in your pbix:

     

    Actual Back Order Qty = 
    SUM(BackOrderedItems[BackOrder])
        -SUM(BackOrderedItems[QtyReserved])
            -Sum(BackOrderedItems[Picked])

     

    2. In table visual "From Back Ordered Items Table", ITEMID should be from table INVENTORYMASTERTABLE.

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      THANK YOU SO MUCH! I pictured that the solution would require an intermediate table and the varResult calculation is exatly what I knew it should be, but my expression-building chops aren't there yet.
      Thank yu for taking the time to share with all of us DAX beginners.  I'm going to try this right now.