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 give...
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.

     

     

     

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.