Calculate cumulative monthly total separate by different years

[gabrielmission7]

Hi there,
I'm trying to calculate monthly total of revenues but the current month's total is added on top of the previous months. Also, I'd like to separate total by years. Using below table example, I'm hoping to get the outcome in Total2 column but someone I'm getting Total1 (in other words, I'd like the cumulative sum to reset in each year). 

Table Sales

Year	Month	Revenue	Total1	Total2
2021	Jan	5	5	5
2021	Feb	10	15	15
2021	Mar	7	22	22
2022	Jan	3	25	3
2022	Feb	4	29	7
2022	Mar	5	34	12

My DAX formula:

Revenue Sum =

CALCULATE(

    SUM('Sales'[Revenue]),

    FILTER(

        ALL('Sales'),

        'Revenue'[Month] <= MAX('Revenue'[Month])    

        )

)

Thank you!

[v-yangliu-msft · Accepted solution]

Hi  @gabrielmission7 ,

Do you have an exact date column like "YYYY/MM/DD" in your data, such as the following:

If there is a date column, you can use Way1:

1. Create measure.

Way1 =
SUMX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))&&'Table'[Date]<=MAX('Table'[Date])),[Revenue])

2. Result:

If there is no date column, try Way2:

1. In Power Query -- Add Column – Index Column – From 1.

2. Create a calculated table.

Text =
SUMMARIZE(
    'Table','Table'[Year],'Table'[Month],    "Index",MINX(FILTER(ALL('Table'),'Table'[Year]=EARLIER('Table'[Year])&&'Table'[Month]=EARLIER('Table'[Month])),[Index]))

3. Create measure.

Way2_value =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Year]=MAX('Text'[Year])&&'Table'[Month]=MAX('Text'[Month])),[Revenue])

Way2_sumvalue =
SUMX(
    FILTER(ALL('Text'),
    'Text'[Year]=MAX('Text'[Year])&&'Text'[Index]<=MAX('Text'[Index])),[Way2_value])

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[gabrielmission7]

Hi @v-yangliu-msft,

Thank you so much for the detailed explanation and example! One thing I noticed is that when we use 'filter' combined with 'All', it seems that other slicers don't seem to be functioning. Is there a way to have slicers (eg. product type) to still be applicable?

[gabrielmission7]

@v-yangliu-msft I solved the issue by replacing the 'All' function with 'Allexcept'. Thanks again for your help!

[gabrielmission7]

@some_bih, further to my earlier comment, I think YTD is the right thing to do. However, for some reason, the outcome is not summing up. Any thoughts? Thank you!

[some_bih]

Hi @gabrielmission7 in initial post you wrote

cumulative sum to reset in each year

This is YTD. Dou yoz have ćcalendar/Date table connected with yourtable?

[gabrielmission7]

Hi @some_bih ,

Thanks for the reply. However, YTD and MTD are probably not what I'm looking for. Essentially, I'm trying to pull this kind of graph that shows cumulative sum of sales but separate by year.

[some_bih]

Hi @gabrielmission7 for monthly amounts per year, you will need TOTALMTD (link below) as possible solution

Measure test MTD= TOTALMTD(SUM('Sales'[Revenue]]),YourCalendar[DateKey]) 

Adjust according your table / column name

If you do not have already calendar table create one as below and connect relationship with your table

https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables 

TOTALMTD

https://learn.microsoft.com/en-us/dax/totalmtd-function-dax?WT.mc_id=DP-MVP-4025372 

TOTALYTD (when you have MTD, usually YTD is pair)

https://learn.microsoft.com/en-us/dax/totalytd-function-dax?WT.mc_id=DP-MVP-4025372 

Measure test yTD= TOTALYTD(SUM('Sales'[Revenue]]),YourCalendar[DateKey]) 

Did I answer your question? Kudos appreciated / accept solution!