cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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!

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors