Conditional Cummulative Sum

[RTERCERO]

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

[sturlaws]

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.

[RTERCERO]

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

[sturlaws]

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,
Sturla

[RTERCERO]

Hi @sturlaws  I sure can:

https://we.tl/t-DYhSvojMKT 

Appreciate the help in advance

RT

[sturlaws]

Nice. 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?

[RTERCERO]

Hi @sturlaws ,

Tables involved:
ANALISIS 

Expected result on column "BALANCE PLANTA ACTUAL":

Attached file with formula made in Excel:

https://we.tl/t-LcrjvvbTeX

Best Regards

RT

[Ashish_Mathur]

Hi,

These calculated column formulas work

calculated Column 1 = LOOKUPVALUE(ANALISIS[BAL. EXPORTAR],ANALISIS[SKU|CNC|COSTURA],CONSOLIDADO[SKU|CNC|COSTURA],ANALISIS[SKU|CNC],CONSOLIDADO[SKU|CNC])

Calculated column 2 = if(CONSOLIDADO[Calculated Column 1]<=-CONSOLIDADO[DOCENAS],CONSOLIDADO[DOCENAS],0)

calculated column 3 = CALCULATE(SUM(CONSOLIDADO[Calculated Column 2]),FILTER(CONSOLIDADO,CONSOLIDADO[SKU|CNC|COSTURA]=EARLIER(CONSOLIDADO[SKU|CNC|COSTURA])&&CONSOLIDADO[SECUENCIA]<EARLIER(CONSOLIDADO[SECUENCIA])))

calculated column 4 = if((CONSOLIDADO[Calculated Column 1]+CONSOLIDADO[Calculated Column 3])<=-CONSOLIDADO[DOCENAS],CONSOLIDADO[DOCENAS],0)

calculated column 5 = CALCULATE(SUM(CONSOLIDADO[Calculated Column 4]),FILTER(CONSOLIDADO,CONSOLIDADO[SKU|CNC|COSTURA]=EARLIER(CONSOLIDADO[SKU|CNC|COSTURA])&&CONSOLIDADO[SECUENCIA]<EARLIER(CONSOLIDADO[SECUENCIA])))

calculated column6 = CONSOLIDADO[Calculated Column 1]+CONSOLIDADO[Calculated Column 5]

The last column will give you your desired result

[RTERCERO]

Hello @Ashish_Mathur ,

It cerainly works, however im looking for a solution with 2 or less columns, you see for my final result will need to evaluate more " PLANTA COSTURA BALANCE" to reasign, so for example if I have 10 " PLANTA COSTURA BALANCE" then in this alternative would have to create 60 columns.

This is why im looking for an aternative using variables.

Apreciate your alternative.

Best Regards

RT