Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Cumulative Calculation Reacting to Date Slicer

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 SolutionDate 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!

2 ACCEPTED SOLUTIONS
Super User

This should work even with a single table.

see attached.

Super User

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])``````
5 REPLIES 5
Super User

This should work even with a single table.

see attached.

Frequent Visitor

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?

Super User

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

Frequent Visitor

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

Super User

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])``````

## Helpful resources

Announcements

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors