Forum Discussion

sohailstsindia's avatar
sohailstsindia
Helper I
8 years ago
Solved

Runing Balance

how we can calulate runing balence amount.   VOUCHER INVOICE Debit Credit Amount Running Balance OPB-001336 23478 0 600 -600   OPB-001363 23692 0 3390 -3390   OPB-001356 ...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi sohailstsindia,

     

    Based on my test, you should be able to just use the formula below to create a new calculated column in your table to get Running Balance. :smileyhappy:

    Running Balance = 
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        ALL ( Table1 ),
        Table1[SUPLIER] = EARLIER ( Table1[SUPLIER] )
            && Table1[INVOICE] <= EARLIER ( Table1[INVOICE] )
    )
)

     

    Regards

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    sohailstsindia

     

    Step#2 Add this Calculated Column

     

    Runnning Balance =
CALCULATE (
    SUM ( TableName[Amount] ),
    FILTER ( ALL ( TableName ), [Index] <= EARLIER ( TableName[Index] ) )
)
  • sohailstsindia's avatar
    sohailstsindia
    Helper I
    8 years ago
    SUPLIERVOUCHERINVOICEDebitCreditAmountRunning Balance
    Sup-001OPB-001336234780600-600 
    Sup-002OPB-0013632369203390-3390 
    Sup-002OPB-00135628092635006350 
    Sup-001OPB-0013552810104240-4240 
    Sup-002OPB-0013572837101800-1800 
    Sup-001OPB-00136130271390003900 
    Sup-002OPB-00133766305002500-2500 
    Sup-001OPB-00134066945501850-1850 
    Sup-002OPB-00134166945603750-3750 
    Sup-004OPB-00133966946001699-1699 

     

    I need Suplier wise, I added index its take lot of time to refresh and get meesage no enough memory, can any other way without index.

     

    SUPLIERVOUCHERINVOICEDebitCreditAmountRunning Balance
    Sup-001OPB-001336234780600-600-600
    Sup-001OPB-0013552810104240-4240-4840
    Sup-001OPB-00136130271390003900-940
    Sup-001OPB-00134066945501850-1850-2790
    Sup-001OPB-00133966946001699-1699-4489
    • v-ljerr-msft's avatar
      v-ljerr-msft
      Microsoft Employee
      8 years ago

      Hi sohailstsindia,

       

      Based on my test, you should be able to just use the formula below to create a new calculated column in your table to get Running Balance. :smileyhappy:

      Running Balance = 
CALCULATE (
    SUM ( Table1[Amount] ),
    FILTER (
        ALL ( Table1 ),
        Table1[SUPLIER] = EARLIER ( Table1[SUPLIER] )
            && Table1[INVOICE] <= EARLIER ( Table1[INVOICE] )
    )
)

       

      Regards

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      8 years ago

      Hi,

       

      Try this calculated column formula

       

      =CALCULATE(SUM(Data[Amount]),FILTER(Data,Data[SUPLIER]=EARLIER(Data[SUPLIER])&&Data[INVOICE]<=EARLIER(Data[INVOICE])))