Forum Discussion
Only Return Work Order Numbers After Amount Is Distributed
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.
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!
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.
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 )