cancel
Showing results 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.

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."

8 REPLIES 8
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 =
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 =
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.

Helper 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

Resident Rockstar

Hi, @RTERCERO,

could you share your pbix-file? Or, if that is not possible, create an example file reproducing your issue?

Cheers,
Sturla

Helper I

Hi @sturlaws  I sure can:

RT

Resident Rockstar

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?

Helper I

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

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.

Best Regards

RT

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors