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.

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:

 SnapShotDate Week CapacityForDay CapacityKg 12.4.2023 202335 29.09.2023 15 12.4.2023 202334 22.09.2023 4 12.4.2023 202333 15.09.2023 12 ... ... ... ... 30-50 SnapshotDates 15-25 weeks for each snapshot - many snapshots will have rows for the same weeks The 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)

 202333 202334 202335 ... Week X Some Value A 202334Value+A=B 202335Value+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
Community Support

You can try the following methods.
Measure:

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

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.

4 REPLIES 4
Community Support

You can try the following methods.
Measure:

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

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.

New Member

Hi

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

CALCULATE (
SUM(Table[CapacityKg]),
FILTER (
AllSelected( Table),
))

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.

Super User

From what you described above, a simple SUM(Table[CapacityKg]) would do the trick.

New Member

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

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