Forum Discussion
Running Balance
Hi,
I am quite new to DAX so am looking for a bit of help with a running balance query.
I am trying to graph out running balances of multiple bank accounts from csv files output from a third party application.
The csv contains data such as Bank (Bank A, Bank B etc), transaction details, debit, credit.
I have added a calcuated column where NET = debit + credit, thus providing the financial movement on that transaction.
I have three bank accounts that need mapping out in aggregate, i.e. Bank B + Bank K + Bank L or singularly (anticipating I will do this via a slicer).
I have started to create a calculated column for the running balance as follows:-
| A/C | DATE | TRANS | REF | DEBIT | CREDIT | CALC1 | CALC2 | CALC3 |
| BANKB | £1,934,682.00 | |||||||
| BANKB | 01/07/2023 | PAYMENT | CARD | £0.00 | -£17.00 | £1,934,665.00 | -£17.00 | -£17.00 |
| BANKK | 02/07/2023 | RECEIPT | BACS | £0.00 | -£9,490.98 | £1,925,174.02 | ||
| BANKK | 02/07/2023 | RECEIPT | BACS | £9,490.98 | £0.00 | £1,934,665.00 | ||
| BANKK | 02/07/2023 | JOURNAL | INT | £9,490.98 | £0.00 | £1,944,155.98 | ||
| BANKL | 02/07/2023 | JOURNAL | JNLS | £4,292.57 | £0.00 | £1,948,448.55 | £13,766.55 | £13,783.55 |
| BANKB | 03/07/2023 | RECEIPT | bacs | £118,378.46 | £0.00 | £2,066,827.01 | ||
| BANKB | 03/07/2023 | PAYMENT | DD | £0.00 | -£3,512.23 | £2,063,314.78 | £128,632.78 | £114,866.23 |
| BANKB | 04/07/2023 | PAYMENT | JUL001 | £0.00 | -£10,503.06 | £2,052,811.72 | ||
| BANKB | 04/07/2023 | PAYMENT | MARGIN | £0.00 | -£6,364.37 | £2,046,447.35 | ||
| BANKB | 04/07/2023 | PAYMENT | TRANS | £100,000.00 | £0.00 | £2,146,447.35 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL002 | £0.00 | -£520,052.50 | £1,626,394.85 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL003 | £0.00 | -£51,275.17 | £1,575,119.68 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL005 | £0.00 | -£3,696.51 | £1,571,423.17 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL006 | £0.00 | -£9,687.88 | £1,561,735.29 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL007 | £0.00 | -£15,062.68 | £1,546,672.61 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL008 | £0.00 | -£709.68 | £1,545,962.93 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL009 | £0.00 | -£1,065.90 | £1,544,897.03 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL010 | £0.00 | -£4,335.61 | £1,540,561.42 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL011 | £0.00 | -£25,283.37 | £1,515,278.05 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL012 | £0.00 | -£1,518.56 | £1,513,759.49 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL013 | £0.00 | -£26,349.30 | £1,487,410.19 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL014 | £0.00 | -£32,100.74 | £1,455,309.45 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL015 | £0.00 | -£3,394.80 | £1,451,914.65 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL016 | £0.00 | -£16,839.29 | £1,435,075.36 | ||
| BANKB | 04/07/2023 | PAYMENT | JUL017 | £0.00 | -£9,729.87 | £1,425,345.49 | ||
| BANKB | 04/07/2023 | PAYMENT | YGDKSE | £0.00 | -£18,000.00 | £1,407,345.49 | ||
| BANKB | 04/07/2023 | RECEIPT | bacs | £48,605.94 | £0.00 | £1,455,951.43 | ||
| BANKK | 04/07/2023 | PAYMENT | TRANS | £0.00 | -£100,000.00 | £1,355,951.43 | -£578,730.57 | -£707,363.35 |
Hello! I suggest creating your calculated (NET) column in Power Query instead or creating a measure for it instead, it will be better for performance. For the running total, try creating the below meausure:
*This assumes you have a measure for NET, so even if you leave NET as a column, still make an explicit measure to SUM [NET]
Measure =TOTALYTD([NET], 'Date'[Date])Based on above I can get a graph like this:
4 Replies
Hello! I suggest creating your calculated (NET) column in Power Query instead or creating a measure for it instead, it will be better for performance. For the running total, try creating the below meausure:
*This assumes you have a measure for NET, so even if you leave NET as a column, still make an explicit measure to SUM [NET]
Measure =TOTALYTD([NET], 'Date'[Date])Based on above I can get a graph like this:Hi Audrey,
Just tried your suggested solution, now that I am back in the office.
It works like a charm - bless you!Many thanks
Vince
You're very welcome! I'm happy to help!
Please mark as a solution if this solved your issue so that others may find it in the future. Have a great week!
