topic Re: Only Return Work Order Numbers After Amount Is Distributed in DAX Commands and Tips

Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Wed, 08 Feb 2023 15:04:35 GMT

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

Anonymous

Thanks so much,

Lydia

Re: Only Return Work Order Numbers After Amount Is Distributed

johnt75 — Wed, 08 Feb 2023 10:38:26 GMT

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

Re: Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Wed, 08 Feb 2023 16:25:04 GMT

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

Re: Only Return Work Order Numbers After Amount Is Distributed

johnt75 — Wed, 08 Feb 2023 16:37:55 GMT

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

Re: Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Wed, 08 Feb 2023 17:55:28 GMT

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

Re: Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Wed, 08 Feb 2023 18:21:56 GMT

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

Re: Only Return Work Order Numbers After Amount Is Distributed

johnt75 — Thu, 09 Feb 2023 09:55:20 GMT

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.

Re: Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Tue, 14 Feb 2023 18:02:50 GMT

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!

Re: Only Return Work Order Numbers After Amount Is Distributed

johnt75 — Wed, 15 Feb 2023 09:14:03 GMT

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 )

Re: Only Return Work Order Numbers After Amount Is Distributed

Anonymous — Wed, 15 Feb 2023 16:58:02 GMT

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.