Sum for any period

[kongyuancn]

Hi there,

I have a small table like this:

And I want to calculate sum of all previous date. I created a calculated column:

TotalTillThisDate = CALCULATE(SUMX('Table','Table'[Quantity]),FILTER('Table','Table'[Date]<=EARLIER('Table'[Date])))

Then it looks like this:

Everything looks fine. But when I want to add a slicer to show a small period, for example, 1/2-1/4, it looks like as following:

The first number 5 is sum of 1/1 and 1/2. This is NOT wha I want (I don't want 1/1 data calculated in this chart). How to make the data strictly limited to the slicer limits? (In this example, I want to get 3, 8, 11 when I set slicer from 1/2 to 1/4)

Thanks in advance.

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/oc067783ze89yrr/kongyuan.pbix?dl=0 

[ryan_mayu · Accepted solution]

@kongyuancn 

if you don't have a date table,you can try this

Measure = 
VAR _min= CALCULATE(min('Table'[Date]),ALLSELECTED('Table'[Date]))
VAR _max= max('Table'[Date])
return CALCULATE(SUM('Table'[Quantity]),FILTER(ALL('Table'),'Table'[Date]>=_min&&'Table'[Date]<=_max))

[kongyuancn]

Thank you @ryan_mayu . That's exactly what I want.

[ryan_mayu]

you are welcome

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/oc067783ze89yrr/kongyuan.pbix?dl=0 

[kongyuancn]

Thank you @Jihwan_Kim That's exact what I want.