Incorrect Running Total Balance

[Sekhar1]

Hi team,

I am struggling to create Running Balance and I am getting just Balance but not Running Balance to Date.

Running Balance result shall be as per "Running Balance Column" highlighted in RED. 

I have included sample data in below table. Can you please assist. 

Thanks in Advance. 

Date	Invoice Number	Credit	Debit	RUNNING BALANCE	RUNNING BALANCE SHALL BE CALCULATED AS PER BELOW
01/01/2018	1	291,848	0	291,848	C2-D2
02/01/2018	2	211,247	0	503,095	C2+C3-D2-D3
03/01/2018	3	156,019	0	659,114	C2+C3+C4-D2-D3-D4
01/02/2019	4	0	10,244	648,870	C2+C3+C4+C5-D2-D3-D4-D5
05/02/2019	5	0	11,112	637,757
06/02/2019	6	0	13,218	624,538
07/02/2019	7	100,000	10,142	714,396
22/05/2020	8	0	3,882	710,513
24/06/2021	9	0	3,182	707,331
10/07/2021	10	100,000	498	806,833

[amitchandak · Accepted solution]

@Sekhar1 , with help from date table; with out without window function

without window

Cumm = CALCULATE( SUM(Table[Credit]) - Sum(Table[Debit]) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(SUM(Table[Credit]) - Sum(Table[Debit]) ,filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

[Sekhar1]

@amitchandak,@ppm1 

Thanks for your response. It did work however if there are multiple transactions in same day, it is showing only 1 Running balance for the particular day by subtracting all Debits happened on that day rather than doing it one by one (See Red values in Running Balance column).  I would like to see final result as shown in green values under "Running Balance (Expected Result)" in below table. Can you please let me know how to rectify it. 

Date	Invoice Number	Credit	Debit	RUNNING BALANCE	RUNNING BALANCE (EXPECTED RESULT)	RUNNING BALANCE SHALL BE CALCULATED AS PER BELOW
01/01/2018	1	291,848	0	291,848	291,848	C2-D2
02/01/2018	2	211,247	0	503,095	503,095	C2+C3-D2-D3
03/01/2018	3	156,019	0	659,114	659,114	C2+C3+C4-D2-D3-D4
01/02/2019	4	0	10,244	648,870	648,870	C2+C3+C4+C5-D2-D3-D4-D5
05/02/2019	5	0	11,112	637,757	637,757
06/02/2019	6	0	13,218	624,538	624,538
07/02/2019	7	100,000	10,142	714,396	714,396
22/05/2020	8	0	3,882	710,513	710,513
24/06/2021	9	0	3,182	707,331	707,331
10/07/2021	10	100,000	498	806,833	806,833
10/08/2021	11	0	200	805,833	806,633
10/08/2021	12	0	300	805,833	806,333
10/08/2021	13	0	200	805,833	806,133
10/08/2021	14	0	100	805,833	806,033
10/08/2021	15	0	200	805,833	805,833

Thanks for your help in advance.

[ppm1 · Accepted solution]

Please try this one instead.

RB2 =
VAR thisinvoice =
    MAX ( T3[Invoice Number] )
RETURN
    CALCULATE (
        SUMX ( T3, T3[Credit ] - T3[Debit] ),
        ALL ( T3 ),
        T3[Invoice Number] <= thisinvoice
    )

Pat

[ppm1]

Here is a measure expression that shows one way to do it.

RB =
VAR thisdate =
    MAX ( T3[Date ] )
RETURN
    CALCULATE (
        SUMX ( T3, T3[Credit ] - T3[Debit] ),
        ALL ( T3 ),
        T3[Date ] <= thisdate
    )

Pat

[amitchandak · Accepted solution]

@Sekhar1 , with help from date table; with out without window function

without window

Cumm = CALCULATE( SUM(Table[Credit]) - Sum(Table[Debit]) ,filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm = CALCULATE(SUM(Table[Credit]) - Sum(Table[Debit]) ,filter(allselected(date),date[date] <=max(date[Date])))

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41

Window

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc