Runing Balance

[sohailstsindia]

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	28092	6350	0	6350
OPB-001355	28101	0	4240	-4240
OPB-001357	28371	0	1800	-1800
OPB-001361	30271	3900	0	3900
OPB-001337	663050	0	2500	-2500
OPB-001340	669455	0	1850	-1850
OPB-001341	669456	0	3750	-3750
OPB-001339	669460	0	1699	-1699

After calulation

VOUCHER	INVOICE	Debit	Credit	Amount	Running Balance
OPB-001336	23478	0	600	-600	-600
OPB-001363	23692	0	3390	-3390	-3990
OPB-001356	28092	6350	0	6350	2360
OPB-001355	28101	0	4240	-4240	-1880
OPB-001357	28371	0	1800	-1800	-3680
OPB-001361	30271	3900	0	3900	220
OPB-001337	663050	0	2500	-2500	-2280
OPB-001340	669455	0	1850	-1850	-4130
OPB-001341	669456	0	3750	-3750	-7880
OPB-001339	669460	0	1699	-1699	-9579

[Zubair_Muhammad]

@sohailstsindia

Step#1 Add an index Column using Query Editor as shown below

[sohailstsindia]

SUPLIER	VOUCHER	INVOICE	Debit	Credit	Amount	Running Balance
Sup-001	OPB-001336	23478	0	600	-600
Sup-002	OPB-001363	23692	0	3390	-3390
Sup-002	OPB-001356	28092	6350	0	6350
Sup-001	OPB-001355	28101	0	4240	-4240
Sup-002	OPB-001357	28371	0	1800	-1800
Sup-001	OPB-001361	30271	3900	0	3900
Sup-002	OPB-001337	663050	0	2500	-2500
Sup-001	OPB-001340	669455	0	1850	-1850
Sup-002	OPB-001341	669456	0	3750	-3750
Sup-004	OPB-001339	669460	0	1699	-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.

SUPLIER	VOUCHER	INVOICE	Debit	Credit	Amount	Running Balance
Sup-001	OPB-001336	23478	0	600	-600	-600
Sup-001	OPB-001355	28101	0	4240	-4240	-4840
Sup-001	OPB-001361	30271	3900	0	3900	-940
Sup-001	OPB-001340	669455	0	1850	-1850	-2790
Sup-001	OPB-001339	669460	0	1699	-1699	-4489

[Ashish_Mathur]

Hi,

Try this calculated column formula

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

[v-ljerr-msft · Accepted solution]

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. 

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

Regards

[Zubair_Muhammad]

@sohailstsindia

Step#2 Add this Calculated Column

Runnning Balance =
CALCULATE (
    SUM ( TableName[Amount] ),
    FILTER ( ALL ( TableName ), [Index] <= EARLIER ( TableName[Index] ) )
)

[Zubair_Muhammad]

@sohailstsindia