Forum Discussion
RTERCERO
3 years agoHelper I
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 ...
sturlaws
3 years agoResident Rockstar
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.
- RTERCERO3 years agoHelper I
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