Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
Mattcr01
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)

DateValueDesired Solution
Date Slicer: 02/01/23 -> 08/01/23
 
01/01/20231  
02/01/202322Plot these values
03/01/202335
04/01/202349
05/01/2023514
06/01/2023620
07/01/2023727
08/01/2023835
09/01/20239  
10/01/202310  


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

 

Thanks in advance!

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

This should work even with a single table. 

see attached.

 

View solution in original post

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

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

This should work even with a single table. 

see attached.

 

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?

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

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:

DateGroupValueSlicer 03/01/2023 -> 06/01/2023
01/02/2023A1 
01/02/2023B2 
01/02/2023C3 
02/01/2023A1 
02/01/2023B2 
02/01/2023C3 
03/01/2023A11
03/01/2023B22
03/01/2023C33
04/01/2023A12
04/01/2023B24
04/01/2023C36
05/01/2023A13
05/01/2023B26
05/01/2023C39
06/01/2023A14
06/01/2023B28
06/01/2023C312
07/01/2023A1 
07/01/2023B2 
07/01/2023C3 
08/01/2023A1 
08/01/2023B2 
08/01/2023C3 
09/01/2023A1 
09/01/2023B2 
09/01/2023C3 
10/01/2023A1 
10/01/2023B2 
10/01/2023C3 

 

 

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
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors