Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    1 year ago

    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.

     

     

     

     

    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] )
    )
)

     

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    thanks Jihwan_Kim thats exactly what I wanted ! youre great!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    1 year ago

    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.

     

     

     

     

    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] )
    )
)

     

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi Anonymous  - 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