Calculate a running total with an opening balance

[ansa_naz]

I have the following example data:

Date	Amount	Running Total
01/08/2018	16111	16111
01/09/2018	15070	31181
01/10/2018	7361	38542
01/11/2018	4600	43142
01/12/2018	24862	68004
01/01/2019	10200	78204
01/02/2019	2320	80524
01/03/2019	13692	94216
01/04/2019	5938	100154
01/05/2019	2656	102810
01/06/2019	19550	122360
01/07/2019	15363	137723
01/08/2019	22155	159878
01/09/2019	22426	182304
01/10/2019	18693	200997
01/11/2019	12066	213063
01/12/2019	11414	224477
01/01/2020	3130	227607
01/02/2020	9849	237456
01/03/2020	7908	245364
01/04/2020	12724	258088

My running total calculatiuon works fine if I show all the data in a chart:

Running Total = 
CALCULATE(
	SUM('Stats'[Amount]),
	FILTER(
		ALL('Stats'[Date]),
		ISONORAFTER('Stats'[Date], MAX('Stats'[Date]), DESC)
	)
)

However I want to show all data for the last 12 months and all data in the future:

Date	Amount	Running Total
01/12/2018	24862	68004
01/01/2019	10200	78204
01/02/2019	2320	80524
01/03/2019	13692	94216
01/04/2019	5938	100154
01/05/2019	2656	102810
01/06/2019	19550	122360
01/07/2019	15363	137723
01/08/2019	22155	159878
01/09/2019	22426	182304
01/10/2019	18693	200997
01/11/2019	12066	213063
01/12/2019	11414	224477
01/01/2020	3130	227607
01/02/2020	9849	237456
01/03/2020	7908	245364
01/04/2020	12724	258088

Any ideas how I could go about doing that, and have the running total formula show the correct amount?

Cheers for all help

[az38]

Hi @ansa_naz 

try a measure

Running Total Measure = 
var startPeriod = DATE(YEAR(EOMONTH(TODAY();-12));MONTH(EOMONTH(TODAY();-12));1)
return CALCULATE(
	SUM('Stats'[Amount]);
	FILTER(
		ALL('Stats'[Date]);
		ISONORAFTER('Stats'[Date]; MAX('Stats'[Date]);DESC) && 'Stats'[Date]>startPeriod
	)
)

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[ansa_naz]

Hi @az38  if I use this then the Running Total does not include the Amount from before 12 months ago - in effect the Running Total resets from 12 months ago, but it should include all values before that date too, just not display it

[az38]

@ansa_naz 

really? i think its exactly running total of amount. or how do you wanna calculate it?

do not hesitate to give a kudo to useful posts and mark solutions as solution

[ansa_naz]

Hi @az38  if I use this then the Running Total does not include the Amount from before 12 months ago - in effect the Running Total resets from 12 months ago, but it should include all values before that date too, just not display it

[ansa_naz]

I think the below makes more sense?

[az38 · Accepted solution]

@ansa_naz 

try

Running Total Measure = 
var startPeriod = DATE(YEAR(EOMONTH(TODAY();-12));MONTH(EOMONTH(TODAY();-12));1)
return 
if(
SELECTEDVALUE(Stats[Date])>startPeriod;
CALCULATE(
	SUM('Stats'[Amount]);
	FILTER(
		ALL('Stats'[Date]);
		ISONORAFTER('Stats'[Date]; MAX('Stats'[Date]);DESC) 
	)
);
BLANK()
)

[ansa_naz]

Brilliant! Cheers @az38 !