Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
martysk
Helper I
Helper I

cumulative sum

Hi all,

 

I have following table

 

yearmonthsales
2024january10
2024february20
2024march30
2024april40
2024may50
2024june60
2024july70
2024august80
2024september90
2024october100
2024november110
2024december120
2025january15
2025february20
2025march534
2025april81
2025may153
2025june32
2025july78
2025august4254
2025september12
2025october45
2025november12
2025december454

 

What I want to achieve with DAX is to calculate Cumulative values per each month within the year. But I want to keep January as is, so the sum will start from february. 

So:

January = January

February = January + February

March = February + March

etc....

 

I want to achieve this:

 

yearmonthsalesCumulative
2024january1010
2024february2030
2024march3060
2024april40100
2024may50150
2024june60210
2024july70280
2024august80360
2024september90450
2024october100550
2024november110660
2024december120780
2025january1515
2025february2035
2025march534569
2025april81650
2025may153803
2025june32835
2025july78913
2025august42545167
2025september125179
2025october455224
2025november125236
2025december4545690
1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1737703371246.png

 

 

Jihwan_Kim_0-1737703285305.png

 

Sales: = 
SUM(sales[sales])

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Cumulative sales: = 
CALCULATE (
    [Sales:],
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'calendar'[Year], 'calendar'[Month name], 'calendar'[Month number] ),
        ORDERBY ( 'calendar'[Month number], ASC ),
        ,
        PARTITIONBY ( 'calendar'[Year] )
    )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

3 REPLIES 3
martysk
Helper I
Helper I

thanks @Jihwan_Kim thats exactly what I wanted ! youre great!

rajendraongole1
Super User
Super User

Hi @martysk  - create a new column as below

MonthOrder =
SWITCH(
cummunl[month],
"january", 1,
"february", 2,
"march", 3,
"april", 4,
"may", 5,
"june", 6,
"july", 7,
"august", 8,
"september", 9,
"october", 10,
"november", 11,
"december", 12
)

 

 

 

 

Cumulative =
VAR CurrentMonthOrder = MAX(cummunl[MonthOrder])
VAR CurrentYear = MAX(cummunl[year])
RETURN
    IF(
        CurrentMonthOrder = 1, -- Keep January as is
        SUM(cummunl[sales]),
        SUMX(
            FILTER(
                cummunl,
                cummunl[year] = CurrentYear &&
                cummunl[MonthOrder] <= CurrentMonthOrder
            ),
            cummunl[sales]
        )
    )

 

 

Now update your Cumulative measure to use the new MonthOrder column for proper comparisons

 

rajendraongole1_0-1737703557842.png

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_1-1737703371246.png

 

 

Jihwan_Kim_0-1737703285305.png

 

Sales: = 
SUM(sales[sales])

 

WINDOW function (DAX) - DAX | Microsoft Learn

 

Cumulative sales: = 
CALCULATE (
    [Sales:],
    WINDOW (
        1,
        ABS,
        0,
        REL,
        ALL ( 'calendar'[Year], 'calendar'[Month name], 'calendar'[Month number] ),
        ORDERBY ( 'calendar'[Month number], ASC ),
        ,
        PARTITIONBY ( 'calendar'[Year] )
    )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.