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
HopefulQuestion
New Member

Cumulative Sum Measure from historical data based on a past date

Hi

 

I have data like this saved for various dates in history:

 

SnapShotDateWeekCapacityForDayCapacityKg
12.4.202320233529.09.202315
12.4.202320233422.09.20234
12.4.202320233315.09.2023

12

............
30-50 SnapshotDates15-25 weeks for each snapshot - many snapshots will have rows for the same weeksThe capacities are on day level, but users will always view their sum on week level. So this date always belongs to the week specified in the Week column Capacity varies

 

The idea is that user will select which Snapshotdate data they will view (so always only 1 SnapshotDate is selected), and then they can view what was the capacity forecast for the various weeks on that Date.

I have similar data for 5 other numerical attributes besides Capacity, but they are not relevant here.

 

I am trying to create a cumulative sum Measure for capacity of all future weeks from that SnapshotDate forwards, and I just cannot get it right. All snapshots are from mondays (the date in the table above might not be a monday, its just a random example). 

 

Example of expected result in PowerBI:

SnapShotDate= 12.04.2023 (selected filter)

202333202334202335...Week X
Some Value A202334Value+A=B202335Value+B=C Sum of all rows with the selected week X, where CapacityForDay >=SnapShotDate + Sum of previous weeks before X

*Edited message, as original example was really bad and didnt highlight the need is cumulative sum based on the progressing weeks, with the additional conditions Table[CapacityForDay ]>=Table[SnapshotDate] and selected date 12.04.2023.

 

I have tried dozens of variations of this; 


CALCULATE (
SUM(Table[CapacityKg]),
FILTER (
ALLEXCEPT(Table,Table[SnapshotDate]),
Table[Week] <= MAX ( Table[Week] )
), Table[CapacityForDay ]>=Table[SnapshotDate]

)

 

with changing the ALLEXCEPT /ALL / FILTER, but never getting the result I need.

I have a feeling the adjustment needed for that is very small, but for some reason I just cannot get it right.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

Hi, @HopefulQuestion 

 

You can try the following methods.
Measure:

Cumulative Sum = CALCULATE(SUM('Table'[CapacityKg]),FILTER(ALL('Table'),[Week]<=SELECTEDVALUE('Table'[Week])))

vzhangti_0-1700116668065.png

Is this the result you expected? If not, please provide more example data and desired results.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

4 REPLIES 4
v-zhangti
Community Support
Community Support

Hi, @HopefulQuestion 

 

You can try the following methods.
Measure:

Cumulative Sum = CALCULATE(SUM('Table'[CapacityKg]),FILTER(ALL('Table'),[Week]<=SELECTEDVALUE('Table'[Week])))

vzhangti_0-1700116668065.png

Is this the result you expected? If not, please provide more example data and desired results.

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi

that worked, and I also got this to work just before I read your message:

CALCULATE (
SUM(Table[CapacityKg]),
FILTER (
AllSelected( Table),
LoadGraphHistory[Week] <= MAX( Table[Week] )
))

So this is solved, thank you!

 

My solution with Allselected isnt as good in many situations (depending on the use case, obviously), so I might use your solution instead. 

sjoerdvn
Super User
Super User

From what you described above, a simple SUM(Table[CapacityKg]) would do the trick. 
If it doesn't you should add some more info on the actual data and the visuals.

Apologies for the horribly unclear example: I tried to make it more clear that I want a cumulative sum based on the weeks.  

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