Running Balance

[vincegotts]

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

[vincegotts]

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

[audreygerred]

Please mark as a solution if this solved your issue so that others may find it in the future. Have a great week!

[audreygerred]

You're very welcome! I'm happy to help!

[audreygerred · Accepted solution]

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: