Rolling sum of selected dates in slider

Întrebare

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

20 iulie 2012 18:46

Răspunsuri

• 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/

1 august 2012 01:48

Toate mesajele

• You need two date slicers.

22 iulie 2012 18:53
• 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!!

23 iulie 2012 06:56
• 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/

1 august 2012 01:48
• 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 august 2012 12:24
• 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/

3 august 2012 13:16