Forum Discussion
Only Return Work Order Numbers After Amount Is Distributed
You could create a measure like
Shortfall =
SUMX (
WO2_WorkOrderMaterialDetail,
VAR QuantityOnHand =
RELATED ( IM_ItemWarehouse[QuantityOnHand] )
VAR RequiredAmount = WO2_WorkOrderMaterialDetail[QtyCommitted]
VAR CurrentDate = WO2_WorkOrderMaterialDetail[WO_Due_Date]
VAR PreviouslyUsed =
CALCULATE (
SUM ( WO2_WorkOrderMaterialDetail[QtyCommitted] ),
ALLEXCEPT (
WO2_WorkOrderMaterialDetail,
WO2_WorkOrderMaterialDetail[ComponentID]
),
WO2_WorkOrderMaterialDetail[WO_Due_Date] < CurrentDate
)
VAR AvailableStock =
MAX ( QuantityOnHand - PreviouslyUsed, 0 )
RETURN
RequiredAmount - AvailableStock
)
and then use this in a table visual with the work order number, and filter it to only show when the value is > 0
- Anonymous3 years agoNot applicable
Jolly Morning John,
This looks to almost be what I need! 🤘Could you help me finishing it? Here is an example of what I am expecting the table to look like
I have a 26 of these on hand currently and here are all my orders that would need that amount distributed out.
Youll see once you get to WO HY910-1 I already have 25 of the total 26 distributed out. Meaning that there is only 1 left to fill the 2 required (QTYCommited column). So I would expect to see the shortfall amount = 1. Instead I am getting -16. Here is what I am getting in my table visual after dragging all my fields in.Any other suggestions?
Thanks!!
- johnt753 years agoSuper User
Not sure how its returning negative numbers at all. Can you share some sample data via dropbox or Google Drive or similar ?
- Anonymous3 years agoNot applicable
Hey John,
You can find a sample dataset here. I just pulled out a few item codes for you to look at, the one I already mentioned and two more. Also attached a screengrab of what im expecting the other two to look like just like I did for WO 00-201604. Let me know if this link works for you, if not I can try to do it through Google Drive.
Thanks again for the help, we are really stumped!