Forum Discussion

RTERCERO's avatar
RTERCERO
Helper I
3 years ago

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 ...
Need Help
sturlaws's avatar
sturlaws
Resident Rockstar
3 years ago

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's avatar
    RTERCERO
    Helper I
    3 years ago

    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's avatar
      Ashish_Mathur
      Super User
      3 years ago

      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's avatar
        RTERCERO
        Helper I
        3 years ago

        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