Forum Discussion
Incorrect Running Total Balance
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 |
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
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
4 Replies
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
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
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.
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
