topic Re: Running Total except for all buckets greater than current bucket in DAX Commands and Tips

Running Total except for all buckets greater than current bucket

alsm — Tue, 31 Oct 2023 16:38:42 GMT

I have data that looks like

For a given date, I want to calcualte the sum where buckets are greater than equal to current bucket.

bucket = ON, 1M, 2M, .... and they are sorrted using column BucketDisplayOrder

RefDtAsDate	BucketDisplayOrder	Bucket	Total	Run Total Negated
30-Sep-23	1	ON	- 15,214,480,007	13,506,905,040
30-Sep-23	2	1M	- 4,009,095,472	4,325,658,564
30-Sep-23	3	2M	- 2,340,791,557	2,340,701,152
30-Sep-23	4	4M	316,698,698	- 316,743,901
31-Aug-23	1	ON	- 12,025,581,264	3,925,291,987
31-Aug-23	2	1M	- 2,573,944,777	- 441,874,669
31-Aug-23	3	2M	- 2,068,740,731	2,068,650,386
31-Aug-23	4	4M	- 3,015,683,930	3,015,638,757
31-Jul-23	1	ON	- 15,045,532,484	12,139,824,710
31-Jul-23	2	1M	- 2,830,709,505	818,728,299
31-Jul-23	3	2M	1,118,164,354	- 1,118,254,637
31-Jul-23	4	4M	- 2,011,845,783	2,011,800,641

I followed the suggestion here: Solved: Re: Running Total / cumulative by Group - Microsoft Fabric Community

VAR _fg = CALCULATE(-1*SUM(RT[TotalCashflow]), ALLEXCEPT(RT,RT[RefDtAsDate]), RT[BucketDisplayOrder] >= EARLIER(RT[BucketDisplayOrder]) ) return _fg

However, I get error:

Earlier refers to row context that does not exist

what am i missing?

Re: Running Total except for all buckets greater than current bucket

Dangar332 — Tue, 31 Oct 2023 17:40:10 GMT

Hi, alsm

try below

VAR _fg = CALCULATE(-1*SUM(RT[TotalCashflow]), ALLEXCEPT(RT,RT[RefDtAsDate]), RT[BucketDisplayOrder] >= max(RT[BucketDisplayOrder]) ) return _fg

and you can use abs(SUM(RT[TotalCashflow])) instead of -1*SUM(RT[TotalCashflow])

Re: Running Total except for all buckets greater than current bucket

alsm — Wed, 01 Nov 2023 12:00:53 GMT

Hi Dangar332

Thank you max works.

for my understanding

ALLEXCEPT would filter all the dates selected in slicer/filter

After the ALLEXCEPT filter, it would filter all buckets greater than bucket selected in slicer/filter (and if there is no bucket selected then max bucket is default)

PS: cannot use abs() cause it can be positive/negative and it should be invereted. Though in sample data it was happening to be total of -ve number