topic Re: How to Cumulative Sum? in DAX Commands and Tips

How to Cumulative Sum?

Mackie — Wed, 25 Mar 2020 04:18:49 GMT

How can I calculate column "C" from "A" & "B" columns? Thank you.

a	b	c
0	7	7
3	10	17
6	2	19
9	4	23
12	9	32
15	18	50

Re: How to Cumulative Sum?

Greg_Deckler — Wed, 25 Mar 2020 04:44:16 GMT

Well, you cannot unless you have an Index column or the value of A for each row of increases in a sorted fashion. If the latter is indeed the case then you could do this:

c = SUMX(FILTER('Table',[a] <= EARLIER([a])),[b])

Re: How to Cumulative Sum?

Mackie — Wed, 25 Mar 2020 05:02:32 GMT

Thank you, Greg_Deckler . Your formulate works for me.

If you know any page which explains "EARLIER" function, please let me know.

Re: How to Cumulative Sum?

Greg_Deckler — Wed, 25 Mar 2020 14:18:31 GMT

Sure, think of EARLIER as the "current" value for a column in a row. The technical explanation is that it refers to an "earlier" evaluation context. So, in the formula above, the evaluation context that is previous to the evaluation context created by the FILTER statement. You could do the same thing with

c =
VAR __a = [a]
RETURN
SUMX(FILTER('Table',[a] <= __a),[b])

But, it's more typing and I do enough of that so EARLIER is a nice shorthand. Plus, in more complex DAX calculations it avoids nested VAR RETURN statements. Pick your poison I guess.