Only Return Work Order Numbers After Amount Is Distributed

Question

Good Day,&nbsp;I am only wanting to return workorders where we didn't have enough quantityonhand to cover the order.In the example below quantityonhand in IM_ItemWarehouse is 139.&nbsp;&nbsp;I need quantityonhand to be distributed to the QtyCommitted amount in WO2_WorkOrderMaterialDetail in order of earliest to latest WODueDates, this would cover WorkOrder 0082975 leaving 29 left. Then the next earliest date is WorkOrder 0082966 with 108 – leaving me 79 short so I would need to return this (0082966) workorder. The next few WorkOrders have 0 committed so they are not in the report, then it shows WorkOrders 0083655 &amp; 0083646 being short 50 &amp; 42 because there is no qty left to cover it. So what I would need returned are WorkOrders&nbsp;0082966,&nbsp;0083655 &amp; 0083646 with their quantity short amounts. Does anyone know how I can accomplish this?IM_ItemWarehouse&nbsp;&nbsp;WO2_WorkOrderMaterialDetailExpected outcome&nbsp;Anonymous&nbsp;&nbsp;Thanks so much,Lydia

johnt75 · Answer

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] &lt; 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 &gt; 0

johnt75 · Answer

OK, one of the issues is that the date isn't a low enough granularity to do comparisons on, because there are multiple orders for the same item on the same day. You need another column, preferably numeric, which is unique for each row and can be used to determine the order. If you don't have one in the source data you can create one in Power Query. I did it by sorting the table by date and then work order with the M code below
let
    Source = Excel.Workbook(File.Contents(""), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"WorkOrder", type text}, {"PreviousIndexNumber", Int64.Type}, {"LinkToNextLine", Int64.Type}, {"Revision", type text}, {"StepNumber", Int64.Type}, {"UM", type text}, {"ItemDescription", type text}, {"Whse", Int64.Type}, {"ComponentItemNumber", type text}, {"QtyParent", Int64.Type}, {"UMconversion", Int64.Type}, {"UnitCost", type number}, {"ScrapPercent", Int64.Type}, {"ExtdQtyRequired", Int64.Type}, {"QtyIssued", Int64.Type}, {"QtyCommitted", Int64.Type}, {"DirectCosts", Int64.Type}, {"FixedOvhdCosts", Int64.Type}, {"VariableOvhdCosts", Int64.Type}, {"StdFixedOverheadAmount", Int64.Type}, {"StdVarOverheadAmount", Int64.Type}, {"WODueDate_CC", type date}, {"OrderStatus", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"WODueDate_CC", Order.Ascending}, {"WorkOrder", Order.Ascending}}),
    #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1, Int64.Type)
in
    #"Added Index"
Its just the last 2 steps you need really.
Once you have that column you can change the measure to be
Shortfall = SUMX(
	'WO2_WorkOrderMaterialDetail',
	VAR QuantityOnHand =
		RELATED( 'IM_ItemWarehouse'[QuantityOnHand] )
	VAR RequiredAmount =
		'WO2_WorkOrderMaterialDetail'[QtyCommitted]
	VAR CurrentIndex = 'WO2_WorkOrderMaterialDetail'[Index]
	VAR PreviouslyUsed =
		CALCULATE(
			SUM( 'WO2_WorkOrderMaterialDetail'[QtyCommitted] ),
			ALLEXCEPT(
				'WO2_WorkOrderMaterialDetail',
				'WO2_WorkOrderMaterialDetail'[ComponentItemNumber]
			),
			'WO2_WorkOrderMaterialDetail'[Index] &lt; CurrentIndex
		)
	VAR AvailableStock = MAX( QuantityOnHand - PreviouslyUsed, 0 )
	RETURN
		MAX( RequiredAmount - AvailableStock, 0 )
)
This now uses the new index column and I also tweaked the return statement so that it won't return negative numbers.

anonymous · Answer

This doesnt work for me unfortunately, I keep getting an error saying the memory is insufficent to do it:/ Same as it was when I tried to put it in the visual. Thank you for all your help though, you did indeed give the right answer I just think my computer cannot handle this.

anonymous · Answer

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&nbsp;I have a 26 of these on hand currently and here are all my orders that would need that amount distributed out.&nbsp;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?&nbsp;Thanks!!

johnt75 · Answer

Not sure how its returning negative numbers at all. Can you share some sample data via dropbox or Google Drive or similar ?

Forum Discussion

Only Return Work Order Numbers After Amount Is Distributed

9 Replies

Related content

PQ -Date Distribution

FORMAT() returns Month Number

Return all week numbers in a range

Distribution Chart

Normal Distribution