Cumulative sum by date and category

[lucas_canova]

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

[Greg_Deckler · Accepted solution]

@lucas_canova 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

[lucas_canova]

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

[Anonymous]

Hi all,

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

Best Regards,

Stephen Tao

[Greg_Deckler]

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

[lucas_canova]

sorry for that.

[Greg_Deckler · Accepted solution]

@lucas_canova 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

[lucas_canova · Accepted solution]

@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