Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
Check it out now!Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
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!
Solved! Go to 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])
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:
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 |
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])