Conditional and selective sum with filters
Hey guys! how you doing? Just got an insight in my dax code, i've been working on the logic of it for a while and i think im getting somewhere, but still need help. I need the code to return the "out" and "available" status correctly for each order, in a way that it will always start with the earliest order then to the oldest. the reult i got for this actual data is right, but it changes when i change the values, getting wrong results, here is an example of the right result i got.: I have used many calculations and contitions to achieving this result, but the logic is still not complete. look at the result i get when change the values: The correct results for this last one would be available, out, available, out, out, available, out, out. The measure i am using is the "FinalCorrectStatus" you can find it in the file: DistributeStok The code is: FinalCorrectStatus = VAR CurrentItem = SELECTEDVALUE('DB_ORDERS'[Produto]) VAR CurrentOrderNum = SELECTEDVALUE('DB_ORDERS'[Pedido]) VAR StockAvailable = CALCULATE( SUM('Table Estoq'[Estoque Real]), 'Table Estoq'[Produto] = CurrentItem ) //soma normal soma sem condição cumulativamente VAR SomaNormal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) //Filtro de apoio para a SomaMaiorEstoq VAR SomaMaiorApoio = FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ) //soma apenas quando o valor da "SomaNormal" for maior que o estoque VAR SomaMaiorEstoq = SUMX( ADDCOLUMNS( SomaMaiorApoio, "@Acumulado", SUMX( FILTER( SomaMaiorApoio, 'DB_ORDERS'[Pedido] <= EARLIER('DB_ORDERS'[Pedido]) ), 'DB_ORDERS'[Quant. Falta] ) ), VAR TotalAcumulado = [@Acumulado] RETURN IF(TotalAcumulado > StockAvailable, 'DB_ORDERS'[Quant. Falta], 0) ) //diferenca entre A soma cumulativa normal e a soma apenas dos maiores que o estoque VAR Diferenca = SomaNormal - SomaMaiorEstoq //valor resultante de "Diferenca" + a quantidade do item no pedido VAR Dif_mais_QTFALT = Diferenca + SELECTEDVALUE('DB_ORDERS'[Quant. Falta]) //verifica se o estoque já atingiu seu limite, vai somando o que estiver dentro da condição de caber no estoque VAR SomaFinal = SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem && ( SUMX( FILTER( ALLSELECTED('DB_ORDERS'), 'DB_ORDERS'[Pedido] <= CurrentOrderNum && 'DB_ORDERS'[Produto] = CurrentItem ), 'DB_ORDERS'[Quant. Falta] ) <= StockAvailable || (Diferenca + 'DB_ORDERS'[Quant. Falta]) <= StockAvailable ) ), 'DB_ORDERS'[Quant. Falta] ) RETURN IF( ISBLANK(StockAvailable), "out", IF( SomaFinal <= StockAvailable && (Dif_mais_QTFALT <= StockAvailable), "available", "out" ) ) its very complex, and i am almost giving up.. Can anyone help me please? Thankyou
Only Return Work Order Numbers After Amount Is Distributed
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
