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.
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.
ITEMID | Actual Back Order Qty |
UCJV300-160 | 16 |
ITEMNUMBER | AVAILDATE | QTYREMAINING |
UCJV300-160 | 8/16/2021 | 8 |
UCJV300-160 | 8/27/2021 | 6 |
UCJV300-160 | 9/6/2021 | 6 |
UCJV300-160 | 9/22/2021 | 2 |
UCJV300-160 | 9/23/2021 | 12 |
UCJV300-160 | 9/29/2021 | 6 |
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
Solved! Go to Solution.
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.
Proud to be a Super User!
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.
Proud to be a Super User!
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |