PROBLEM: Waterfall chart with change value / Running Total

[jonasabb]

Hello, I am currently working on a waterfall chart. I only have the "change" values. So waterfall chart is not working. I tried to solve this with a running total which is not perfectly working, as it does not show me the running total for the "total" of the given categories. Does anyone have a solution to this? 

This is the basic principle of the table:

Day	Change	Values
01.01.2020	Mix	100
01.04.2020	Mix	0
01.07.2020	Increase	90
01.07.2020	Mix	50
01.07.2020	Decrease	-20
01.12.2020	Mix	0
01.12.2020	Increase	0
01.12.2020	Decrease	0
01.01.2021	Increase	100
01.01.2021	Decrease	-100
01.05.2021	Decrease	-20

I used the following code:

Running Total = 
CALCULATE(
	SUM('Modell (2)'[Values]),
	FILTER(
		ALL('Modell (2)'),
		'Modell (2)'[DAY] <= MAX('Modell (2)'[DAY]) && 'Modell (2)'[Change] = MAX('Modell (2)'[Change]
	)
))

Now it gives me the running total by category (change), but it does not show me the sum of the running totals.

The data should add up in total and within the categories the values should also add up further if there is no value in a quarter.

Hope I could explain the issue

Thanks for the help

Jonas

[lbendlin]

No, that is not possible unless you want to do cross join acrobatics, or add fake 0 value transactions for the gaps.

[jonasabb]

Alright. Still awesome that helps already a lot! 

[lbendlin · Accepted solution]

Sorry about that.

Running Total = 
var d=max('Modell (2)'[DAY])
var c=max('Modell (2)'[Change])
var ov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[Change]=c && 'Modell (2)'[DAY]<=d),'Modell (2)'[Values])
var tov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[DAY]<=d),'Modell (2)'[Values])
return if(isfiltered('Modell (2)'[Change]),ov,tov)

[jonasabb]

Total looks good now but is it also possible to have also the other values copied down? So that in Qrt 2 2021 we see -140,190,150
Then it looks perfect.
Thanks!!!!

[lbendlin]

Try this version instead:

Running Total = 
var d=max('Modell (2)'[DAY])
var c=max('Modell (2)'[Change])
var ov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[Change]=c && 'Modell (2)'[DAY]<=d),'Modell (2)'[Values])
var tov=sumx(filter(ALL('Modell (2)'),'Modell (2)'[DAY]<=d),'Modell (2)'[Values])
return if(HASONEVALUE('Modell (2)'[Change]),ov,tov)

[jonasabb]

Hi @lbendlin ,  thanks a lot for your help and the proposal, looks quite good already, only for the last quarter it shows me the wrong total. I assume it is because the negative value is the only value? How can I fix that? Best would be to have the running total in every line.

Thank you for your help 🙂