Forum Discussion
kongyuancn
Helper II
Helper IISum for any period
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.
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))
5 Replies
kongyuancnThank you Jihwan_Kim That's exact what I want.
ryan_mayuSuper User
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