Forum Discussion

RTERCERO's avatar
RTERCERO
Icon for Helper I rankHelper 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
    Icon for Resident Rockstar rankResident 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
      Icon for Helper I rankHelper 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
        Icon for Super User rankSuper 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