topic Re: Calculate cumulative monthly total separate by different years in DAX Commands and Tips

Calculate cumulative monthly total separate by different years

gabrielmission7 — Thu, 28 Sep 2023 05:53:37 GMT

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!

Re: Calculate cumulative monthly total separate by different years

some_bih — Thu, 28 Sep 2023 06:30:23 GMT

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!

Re: Calculate cumulative monthly total separate by different years

gabrielmission7 — Thu, 28 Sep 2023 17:12:13 GMT

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.

Re: Calculate cumulative monthly total separate by different years

gabrielmission7 — Thu, 28 Sep 2023 18:49:54 GMT

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!

Re: Calculate cumulative monthly total separate by different years

some_bih — Thu, 28 Sep 2023 20:06:45 GMT

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?

Re: Calculate cumulative monthly total separate by different years

Anonymous — Mon, 02 Oct 2023 06:46:46 GMT

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

Re: Calculate cumulative monthly total separate by different years

gabrielmission7 — Thu, 05 Oct 2023 17:52:44 GMT

Hi Anonymous,

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?

Re: Calculate cumulative monthly total separate by different years

gabrielmission7 — Thu, 05 Oct 2023 21:14:50 GMT

Anonymous I solved the issue by replacing the 'All' function with 'Allexcept'. Thanks again for your help!