cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Cumulative sum by date and category

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
2 ACCEPTED SOLUTIONS
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@Greg_Deckler

``````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``````

6 REPLIES 6
Helper I

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
Community Support

Hi all,

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

Best Regards,

Stephen Tao

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

sorry for that.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper I

@Greg_Deckler

``````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``````

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.