Cumulative Calculation Reacting to Date Slicer

[Mattcr01]

Hi All,

After scouring the forums my gut feel is I don't think this is possible but would appreciate confirmation/someone smarter than me quickly having a look!

Ultimately I want to make a cumulative line chart where the cumulative sum reacts to a date slicer.

By that I mean that I am to calculate a cumulative sum, I have managed to do it over groups and static date ranges, however, is it possible to do it so that it reacts to a slicer? As far as I'm aware measures are the only things that re-calculate in reaction to a slicer changing, however, a measure cannot return a table of data so therefore this is impossible?

To include an example, I have a table here of data, imagine I put a date slicer range between 02/01/2023 -> 08/01/2023, my desired result is that the cumulative line that is plotted is a cumulative sum that starts on the 02/01/2023 and ends on the 08/01/2023

(not sure why formatting is weird, the Value column is just a a sequence of 1->10)

Date	Value	Desired Solution Date Slicer: 02/01/23 -> 08/01/23
01/01/2023	1
02/01/2023	2	2	Plot these values
03/01/2023	3	5
04/01/2023	4	9
05/01/2023	5	14
06/01/2023	6	20
07/01/2023	7	27
08/01/2023	8	35
09/01/2023	9
10/01/2023	10

Any help in knowing if this is possible would be greatly appreciated!

Thanks in advance!

[lbendlin · Accepted solution]

This should work even with a single table. 

see attached.

[lbendlin · Accepted solution]

This should work even with a single table. 

see attached.

[Mattcr01]

Thank you! that has worked

How would I adapt this to produce a cumulative sum over a group? For example: for each date I have 8 groups and I want to make the cumulative sum within these?

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Mattcr01]

So if each date had multiple groupings within it I want the cumulative sum to stick to those groups, e.g. slicer set to 03/01/2023 -> 06/01/2023 and there are 3 groups (A/B/C) for each day, I want the cumulative sum of A/B/C across all those selected dates:

Date	Group	Value	Slicer 03/01/2023 -> 06/01/2023
01/02/2023	A	1
01/02/2023	B	2
01/02/2023	C	3
02/01/2023	A	1
02/01/2023	B	2
02/01/2023	C	3
03/01/2023	A	1	1
03/01/2023	B	2	2
03/01/2023	C	3	3
04/01/2023	A	1	2
04/01/2023	B	2	4
04/01/2023	C	3	6
05/01/2023	A	1	3
05/01/2023	B	2	6
05/01/2023	C	3	9
06/01/2023	A	1	4
06/01/2023	B	2	8
06/01/2023	C	3	12
07/01/2023	A	1
07/01/2023	B	2
07/01/2023	C	3
08/01/2023	A	1
08/01/2023	B	2
08/01/2023	C	3
09/01/2023	A	1
09/01/2023	B	2
09/01/2023	C	3
10/01/2023	A	1
10/01/2023	B	2
10/01/2023	C	3

[lbendlin · Accepted solution]

Here's one possible implementation 

cumul = 
var mind = CALCULATE(min('Table'[Date]),ALLSELECTED('Table'[Date]))
var maxd = max('Table'[Date])
var a = ADDCOLUMNS(VALUES('Table'[Group]),"sm",CALCULATE(sum('Table'[Value]),'Table'[Date]>=mind,'Table'[Date]<=maxd))
return sumx(a,[sm])

see attached

Here's another version

cumul = 
var f = filter(ALLSELECTED('Table'),'Table'[Date]<=max('Table'[Date]))
var a = SUMMARIZE(f,[Group],"sm",sum([Value]))
return sumx(a,[sm])