Rolling sum of selected dates in slider

Question

Hi,

I'm trying to do a running sum of the selected dates in a slider. Let's say we've got the following sells:

1/1/2012       10

2/1/2012       20

3/1/2012       15

4/1/2012       10

5/1/2012       15

What I want is to do a rolling sum, so without selecting anything in the sliders we've got:

1/1/2012       10

2/1/2012       10+20=30

3/1/2012       15+30=45

4/1/2012       10+45=55

5/1/2012       15+55=70

However, if I select the days 3/1, 4/1 and 5/1 I want to get:

3/1/2012       15

4/1/2012       10+15=25

5/1/2012       15+25=40

I tried something like: =CALCULATE(SUM(Sales[Amount])); DATESBETWEEN(TimeDim[Day]; FIRSTDATE(ALL(TimeDim[Day])); LASTDATE(TimeDim[Day])))

With FIRSTDATE(ALL(TimeDim[Day]) the sum starts the very first day, without caring about any filter, so it's not what I'm looking for. I've also tried to play with ALLSELECTED() but with no luck.

Can anyone help me? I feel like I'm getting crazy with that...

Thanks in advance!! 

Answer 1

You need two date slicers.

Answer 2

You need two date slicers.

Thanks for your answer, David.

However, I haven't figured out how using two date slicers will solve my problem. Can you explain it a little more, please?

Thanks!!

Answer 3

Hi En Marcus

The following measure will do what you need:

CALCULATE( 
	SUM( Table1[amount] ) , 
	FILTER( 
		ALLSELECTED(Table1[date]) , 
		COUNTROWS( 
			FILTER( Table1, EARLIER(Table1[date]) <= Table1[date]   )  
		) 
	) 
)

The trick is to create a nested row context, so the EARLIER function can be utilized

---

Javier Guillen
http://javierguillen.wordpress.com/

Answer 4

Hi Javier,

Thank you for your answer, it works as expected. It looks like I still need a lot of practice to really understand the EARLIER function and some context behaviours.

You saved me from getting crazy about it, haha.

Thanks!!

Answer 5

You are welcome.

If it helps, here is a blog entry I wrote explaining the use of EARLIER in DAX measures.  I used the same technique in the calculation I gave you.

http://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

---

Javier Guillen
http://javierguillen.wordpress.com/