cumulative sum

[Anonymous]

Hi all,

I have following table

year	month	sales
2024	january	10
2024	february	20
2024	march	30
2024	april	40
2024	may	50
2024	june	60
2024	july	70
2024	august	80
2024	september	90
2024	october	100
2024	november	110
2024	december	120
2025	january	15
2025	february	20
2025	march	534
2025	april	81
2025	may	153
2025	june	32
2025	july	78
2025	august	4254
2025	september	12
2025	october	45
2025	november	12
2025	december	454

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:

year	month	sales	Cumulative
2024	january	10	10
2024	february	20	30
2024	march	30	60
2024	april	40	100
2024	may	50	150
2024	june	60	210
2024	july	70	280
2024	august	80	360
2024	september	90	450
2024	october	100	550
2024	november	110	660
2024	december	120	780
2025	january	15	15
2025	february	20	35
2025	march	534	569
2025	april	81	650
2025	may	153	803
2025	june	32	835
2025	july	78	913
2025	august	4254	5167
2025	september	12	5179
2025	october	45	5224
2025	november	12	5236
2025	december	454	5690

[Anonymous]

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

[rajendraongole1]

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

[Jihwan_Kim · Accepted solution]

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