topic Re: Cumulative sum by date and category in DAX Commands and Tips

Cumulative sum by date and category

Anonymous — Fri, 19 Jul 2024 18:31:00 GMT

Hi everyone!

I need help. I was trying to perform a dax to return cumulative sum by month and version, in that year.

example sample:

CATEGORY_1	CATEGORY_2	...	CATEGORY_N	DATE	VERSION	VALUE	CUMULATIVE_SUM
X	X		X	01/01/2023	A	1	1
X	X		X	01/01/2023	A	2	3
X	X		X	01/01/2023	B	3	3
X	X		X	01/01/2023	B	4	7
X	X		X	01/02/2023	A	1	4
X	X		X	01/02/2023	A	2	6
X	X		X	01/02/2023	B	3	10
X	X		X	01/02/2023	B	4	14
X	X		X	01/01/2024	A	1	1
X	X		X	01/01/2024	A	2	3
X	X		X	01/01/2024	B	3	3
X	X		X	01/01/2024	B	4	7
X	X		X	01/02/2024	A	1	4
X	X		X	01/02/2024	A	2	6
X	X		X	01/02/2024	B	3	10
X	X		X	01/02/2024	B	4	14

Re: Cumulative sum by date and category

Anonymous — Fri, 19 Jul 2024 18:31:42 GMT

Hi everyone!

I need help. I was trying to perform a dax to return cumulative sum by month and version, in that year.

example sample:

CATEGORY_1	CATEGORY_2	...	CATEGORY_N	DATE	VERSION	VALUE	CUMULATIVE_SUM
X	X		X	01/01/2023	A	1	1
X	X		X	01/01/2023	A	2	3
X	X		X	01/01/2023	B	3	3
X	X		X	01/01/2023	B	4	7
X	X		X	01/02/2023	A	1	4
X	X		X	01/02/2023	A	2	6
X	X		X	01/02/2023	B	3	10
X	X		X	01/02/2023	B	4	14
X	X		X	01/01/2024	A	1	1
X	X		X	01/01/2024	A	2	3
X	X		X	01/01/2024	B	3	3
X	X		X	01/01/2024	B	4	7
X	X		X	01/02/2024	A	1	4
X	X		X	01/02/2024	A	2	6
X	X		X	01/02/2024	B	3	10
X	X		X	01/02/2024	B	4	14

Re: Cumulative sum by date and category

Greg_Deckler — Fri, 19 Jul 2024 18:43:54 GMT

Anonymous To get the breakdown that you have in your CUMULATIVE_SUM column you would need an Index which you would then substitute for DATE in this formula:

Cumulative = VAR __Version = [VERSION] VAR __Date = [DATE] VAR __Table = FILTER( ALL( 'Table' ), [VERSION] = __Version && [DATE] <= __Date ) VAR __Result = SUMX( __Table, [VALUE] ) RETURN __Result

Re: Cumulative sum by date and category

Greg_Deckler — Fri, 19 Jul 2024 18:44:21 GMT

Anonymous Please don't cross-post: Cumulative sum by date and category - Microsoft Fabric Community

Re: Cumulative sum by date and category

Anonymous — Fri, 19 Jul 2024 19:09:08 GMT

sorry for that.

Re: Cumulative sum by date and category

Anonymous — Mon, 22 Jul 2024 12:56:19 GMT

Greg_Deckler

thx for reply,

it worked with follow adjusts:

Cumulative = VAR __Version = SELECTEDVALUE(BASE_TOTAL[VERSAO]) VAR __Date = SELECTEDVALUE(BASE_TOTAL[SAFRA]) VAR __Year = YEAR(__Date) VAR __Table = FILTER( ALL(BASE_TOTAL), [VERSAO] = __Version && YEAR([SAFRA]) = __Year && [SAFRA] <= __Date ) VAR __Result = SUMX( __Table, [VALOR] ) RETURN __Result

Edit:

I ran into a problem working with the previous dax, my slicers werent filtering my cumulative sum chart, so i made a small adjust again:

Cumulative = VAR __Version = SELECTEDVALUE(BASE_TOTAL[VERSAO]) VAR __Date = SELECTEDVALUE(BASE_TOTAL[SAFRA]) VAR __Year = YEAR(__Date) VAR __Table = CALCULATE( SUM(BASE_TOTAL[VALOR]), FILTER( ALL(BASE_TOTAL), [VERSAO] = __Version && YEAR([SAFRA]) = __Year && [SAFRA] <= __Date ) ) RETURN __Table

Re: Cumulative sum by date and category

Anonymous — Mon, 22 Jul 2024 01:39:39 GMT

Hi all,

I have moved the post under this post, please be aware.

Best Regards,

Stephen Tao