Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

cumulative sum

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

     

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