Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
RTERCERO
Helper I
Helper 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 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 8
sturlaws
Resident Rockstar
Resident 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.

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:

RTERCERO_0-1673559631131.png

 

I will try to fix it, but maybe you get to it first.

 

Apreciate the Help alot.

 

Best Regards
RT

sturlaws
Resident Rockstar
Resident Rockstar

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

Hi @sturlaws  I sure can:

 

https://we.tl/t-DYhSvojMKT 

 

Appreciate the help in advance

RT

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?

Hi @sturlaws ,

Tables involved:
ANALISIS 

RTERCERO_0-1672948346374.png


Expected result on column "BALANCE PLANTA ACTUAL":

RTERCERO_1-1672948426329.png

 


Attached file with formula made in Excel:

https://we.tl/t-LcrjvvbTeX

Best Regards

RT

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.