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