Rolling sum of selected dates in slider

# Rolling sum of selected dates in slider

• 20. července 2012 18:46

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...

### Všechny reakce

• 22. července 2012 18:53

You need two date slicers.

• 23. července 2012 6:56

You need two date slicers.

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

Thanks!!

• 1. srpna 2012 1:48

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/

• 3. srpna 2012 12:24

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!!
• 3. srpna 2012 13:16

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/