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:-
Running Balance ALL = (calculate (SUM('All Transactions'[NET]),ALL('All Transactions'[DATE]),'All Transactions'[DATE]<=EARLIER('All Transactions'[DATE])))
When graphing, I can see that it is correctly reporting the daily movement by day but not the cumulative movement.
Data as follows (CALC1, CALC2, CALC3 being my excel calcs)
| 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 |
So my graph is reporting Calc C by day but I need it to report Calc A cumulatively at the end of each day.
Can somebody assist please?
I wonder if I am missing something obvious here?
Thanks
Vince
