Only Return Work Order Numbers After Amount Is Distributed

[Anonymous]

Good Day,

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.  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 & 0083646 being short 50 & 42 because there is no qty left to cover it. So what I would need returned are WorkOrders 0082966, 0083655 & 0083646 with their quantity short amounts. Does anyone know how I can accomplish this?

IM_ItemWarehouse 

WO2_WorkOrderMaterialDetail

Expected outcome

@CalebR 

Thanks so much,

Lydia

[johnt75]

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

[Anonymous]

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!!

[johnt75]

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

[CalebR]

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!

https://filebox.mercervalve.net/sharing/LfCiddHev

[CalebR]

It appears that link isnt working, here it is in google drive: https://drive.google.com/drive/folders/1Vtg1ziC7lYcPFPIDSZ-XKQWSPVyQdd-Q?usp=sharing

[johnt75]

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

[CalebR]

So this works perfectly for us, issue is that this is so demanding it crashes every visual I try to put it in (matrix or table). Could you think of any other route to go about this? Any advicce would be helpful, thank you!

[johnt75]

You could try turning it into a calculated column instead of a measure, that way everything only needs to get calculated during data refresh

Shortfall =
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] < CurrentIndex
    )
VAR AvailableStock =
    MAX ( QuantityOnHand - PreviouslyUsed, 0 )
RETURN
    MAX ( RequiredAmount - AvailableStock, 0 )

[CalebR]

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.