topic Re: Cumulative Sum Measure from historical data based on a past date in DAX Commands and Tips

Cumulative Sum Measure from historical data based on a past date

HopefulQuestion — Tue, 14 Nov 2023 15:33:31 GMT

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.

Re: Cumulative Sum Measure from historical data based on a past date

sjoerdvn — Tue, 14 Nov 2023 15:25:53 GMT

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.

Re: Cumulative Sum Measure from historical data based on a past date

HopefulQuestion — Tue, 14 Nov 2023 15:34:38 GMT

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

Re: Cumulative Sum Measure from historical data based on a past date

v-zhangti — Thu, 16 Nov 2023 06:39:03 GMT

Hi, HopefulQuestion

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.

Re: Cumulative Sum Measure from historical data based on a past date

HopefulQuestion — Tue, 21 Nov 2023 06:46:04 GMT

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.