Forum Discussion
Conditional Cummulative Sum
Hello,
I'm looking to calculate how my inventory applies to may demand on process sequence so if I have over supply for an order change it for another PO.
So I have my balance in another table wich I pull with no issue,
I have a determined sequence to apply inventory from 1 to N
For each line of inventory check if my there is balance, and if it is subtract inventory quantity in the the current row to create new balance if not keep row balance,
The next line check the newly generated balance an perform same operation.
I have tried several alternatives but end up with circular dependency,
Now am trying using variables:
CONDITIONAL RUNNING TOTAL =
VAR BalAnalisis =
SUMX(FILTER(SUMMARIZE(ANALISIS, ANALISIS[SKU|CNC|COSTURA], "BAL. EXPORTAR", SUM(ANALISIS[BAL. EXPORTAR])),
[SKU|CNC|COSTURA] = 'DETALLE DE LOTES'[SKU|CNC|COSTURA]),
[BAL. EXPORTAR])
VAR CurrentDZ = 'DETALLE DE LOTES'[DOCENAS]
VAR BalRow =IF(BalAnalisis<=CurrentDZ,BalAnalisis+CurrentDZ,BalAnalisis)
VAR Accumulation =
CALCULATE (
SUMX (
'DETALLE DE LOTES',
'DETALLE DE LOTES'[BalRow]
),
FILTER (
ALL ( 'DETALLE DE LOTES'),
'DETALLE DE LOTES'[SECUENCIA DE APLICACION] <= MIN ( 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] )
)
)
RETURN
SWITCH(TRUE(),
'DETALLE DE LOTES'[SECUENCIA DE APLICACION]=MINX(ALLEXCEPT('DETALLE DE LOTES','DETALLE DE LOTES'[SKU|CNC|COSTURA]),'DETALLE DE LOTES'[SECUENCIA DE APLICACION]),
BalRow,
Accumulation)
getting the following error:
"Column 'BalRow' in table 'DETALLE DE LOTES' cannot be found or may not be used in this expression."
Please Help
8 Replies
Try this:
create this calculated column:ColumnAUX = VAR _skuCncCost = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SKU|CNC|COSTURA] ) ) VAR _sequence = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] ) ) VAR _skuCnc = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SKU|CNC] ) ) VAR _pcb = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] ) ) VAR _tmp1 = CALCULATE ( SUM ( ANALISIS[BAL. EXPORTAR] ), FILTER ( ALL ( ANALISIS ), ANALISIS[SKU|CNC|COSTURA] = _skuCncCost ) ) VAR _tmp2 = CALCULATE ( SUM ( 'DETALLE DE LOTES'[DOCENAS] ), FILTER ( ALL ( 'DETALLE DE LOTES' ), 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] = _pcb && 'DETALLE DE LOTES'[SKU|CNC] = _skuCnc && 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] < _sequence ) ) VAR _tmp3 = ADDCOLUMNS ( var _skuCnc2=CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[SKU|CNC])) var _pcb2 = CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[PLANTA COSTURA BALANCE])) var _s2 = CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[SECUENCIA DE APLICACION])) return CALCULATETABLE('DETALLE DE LOTES', FILTER ( all('DETALLE DE LOTES'), 'DETALLE DE LOTES'[SKU|CNC] = _skuCnc2 && 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] = _pcb2 && 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] < _s2 )), "tesst", IF ( _tmp1 + _tmp2 <= CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ) * -1, CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ), 0 ) ) RETURN IF ( _tmp1 + _tmp2 <= CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ) * -1, CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ), 0 )and this calculated column:
BALANCE PLANTA ACTUAL = VAR _skuCncCost = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SKU|CNC|COSTURA] ) ) VAR _sequence = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] ) ) VAR _skuCnc = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[SKU|CNC] ) ) VAR _pcb = CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] ) ) VAR _tmp1 = CALCULATE ( SUM ( ANALISIS[BAL. EXPORTAR] ), FILTER ( ALL ( ANALISIS ), ANALISIS[SKU|CNC|COSTURA] = _skuCncCost ) ) VAR _tmp2 = CALCULATE ( SUM ( 'DETALLE DE LOTES'[DOCENAS] ), FILTER ( ALL ( 'DETALLE DE LOTES' ), 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] = _pcb && 'DETALLE DE LOTES'[SKU|CNC] = _skuCnc && 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] < _sequence ) ) VAR _tmp3 = ADDCOLUMNS ( var _skuCnc2=CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[SKU|CNC])) var _pcb2 = CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[PLANTA COSTURA BALANCE])) var _s2 = CALCULATE(SELECTEDVALUE('DETALLE DE LOTES'[SECUENCIA DE APLICACION])) return CALCULATETABLE('DETALLE DE LOTES', FILTER ( all('DETALLE DE LOTES'), 'DETALLE DE LOTES'[SKU|CNC] = _skuCnc2 && 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] = _pcb2 && 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] < _s2 )), "tesst", IF ( _tmp1 + _tmp2 <= CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ) * -1, CALCULATE ( SELECTEDVALUE ( 'DETALLE DE LOTES'[DOCENAS] ) ), 0 ) ) RETURN _tmp1 + CALCULATE(SUM('DETALLE DE LOTES'[ColumnAUX]),filter(ALL('DETALLE DE LOTES'), 'DETALLE DE LOTES'[SKU|CNC] = _skuCnc && 'DETALLE DE LOTES'[PLANTA COSTURA BALANCE] = _pcb && 'DETALLE DE LOTES'[SECUENCIA DE APLICACION] < _sequence ))I got it to work on the sample data you provided.
Hi sturlaws ,
This works very similar to my Excel Formula, but when one row exceed the balance retuns 0 in the rows below even if they "fit" in the open balance, I modified the sample data (https://we.tl/t-dJyXfIGcAx ) so you have an example of the situation and updated the example image:I will try to fix it, but maybe you get to it first.
Apreciate the Help alot.
Best Regards
RT
Hi, RTERCERO,
could you share your pbix-file? Or, if that is not possible, create an example file reproducing your issue?
How to Get Your Question Answered Quickly
Cheers,
SturlaNice. I looked at it, but I don't understand what you are trying to do. Could you, based on the values in the file you provided, create a table in excel or something to show your desired outcome?
