topic Re: Problem with cumulative(running) total in DAX Commands and Tips

Problem with cumulative(running) total

voidbydefault — Fri, 26 May 2023 21:37:39 GMT

Hi,

I am trying to calculate a cumulative/running total using:

Running Invoiced Amounts = VAR CurrentDate = MAX(AR_Balances[GL Date]) RETURN CALCULATE( SUMX(AR_Balances, AR_Balances[Invoice Amount]), FILTER( ALL(AR_Balances), AR_Balances[GL Date] <= CurrentDate ) )

Above gives matrix following output:

GL Date*	Invoice Amount	Cumulative Total
1-Jan-22	100	1,050
15-Jan-22	200	1,250
...	...	...
31-Dec-22	200	1,450
1-Jan-23	250	1,700
...	...	...
27-May-23	300	2,200
Total	1,050	3,050

*GL Date = Invoice Date

Problems I am unable to fix::

1. Starting of cumulative total (1,050) is the sum of all invoices (total). Why and how to prevent so?

2. Cumulative total seemingly works (if starting 1,050 is ignored), but I expect it to reset as soon as the year changes on 31-Dec. How to achieve this?

Thanks.

Re: Problem with cumulative(running) total

voidbydefault — Fri, 26 May 2023 21:48:14 GMT

Update:
I have managed to fix the first issue where cumulative total started with the sum of all invoices (i.e. 1,050) using below DAX. Please help me with the second problem. Thanks:

Solution to # 1:

Running Invoiced Amounts = VAR CurrentDate = MAX(AR_Balances[GL Date]) RETURN CALCULATE( SUMX(AR_Balances, AR_Balances[Invoice Amount]), AR_Balances[GL Date] <= CurrentDate )

Re: Problem with cumulative(running) total

raksone — Sat, 27 May 2023 00:00:58 GMT

Can you share your PBI?

Re: Problem with cumulative(running) total

voidbydefault — Sat, 27 May 2023 21:32:17 GMT

Nopes. Apologies.