Cumulative sum in a year per month

[Tobias106]

Hello everbody,

I have data per month from 2017 - now.

I want to show a cumulated line per month for e.g. 2021.

So in the x-axis are the month, FEB yhould be JAN + Februar for 2021 and so on.

How can I do this per DAX formula?

Thank you 🙂

Regards Tobias

[v-yadongf-msft · Accepted solution]

Hi @Tobias106 ,

This is my test table:

Please create a new column:

Cumulative sum in a year per month = CALCULATE(SUM('Table'[Sales]),FILTER('Table', 'Table'[Sales] <= EARLIER('Table'[Sales]) && 'Table'[Year] = EARLIER('Table'[Year])))

You can calculate the cumulative sum in a year per month:

Best regards,

Yadong Fang

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

[Tobias106]

@v-yadongf-msft @ Thank you very much for this simple and awesome code, that helps a lot :-).

One additional question. Beside the year and month I also have the date in the table, which can occure more than once.

Therefore I used your code and changed the year to the date:

Cumulative sum in a year per month = CALCULATE(SUM('Table'[Sales]),FILTER('Table', 'Table'[Sales] <= EARLIER('Table'[Sales]) && 'Table'[Date] = EARLIER('Table'[Date])))

But due to to topic that I have sometime the same date 2-3 times (or more), it cumulates in the following way:

Example:

date	Sales	**bleep** Sales
01.01.2022	10	10
01.01.2022	12	22
01.01.2022	14	36

Power Bi shows it now as one date an so 01.01.2022 is 10+22+36 = 68, which is of course wrong.

Which I need - If the same date is more than once, than it should only cumulate in the last date-line, so the following should be:

date	Sales	**bleep** Sales
01.01.2022	10
01.01.2022	12
01.01.2022	14	36

Can you please help me again :)?

[Ashish_Mathur]

Hi,

Please share some data (in format that can be pasted in an MS Excel file) and show the expected result.

[v-yadongf-msft]

Hi  @Tobias106 ，

You need to create some additional columns.

This is my test table:

First, add an index column in Power Query:

Create a new column to count the number of occurrences of each date:

Count = CALCULATE(COUNT('data2'[Date]),FILTER('data2','data2'[Date] = EARLIER('data2'[Date]) && 'data2'[Index] <= EARLIER('data2'[Index])))

The result you want:

Cumulative sum in a year per month = 
var sumsales = CALCULATE(SUM('data2'[Sales]),FILTER('data2','data2'[Sales] <= EARLIER('data2'[Sales])))
return
IF('data2'[Count] = MAXX(FILTER('data2','data2'[Date] = EARLIER('data2'[Date])),'data2'[Count]),sumsales)

Best regards,

Yadong Fang

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

[amitchandak]

@Tobias106 , Either you need have month in YYYYMM format, Make sure you create a month year table , with YYYYMM, Month , Year 

or if you date will do

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Month'),'Month'[YearMonth] <=max('Month'[YearMonth])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Month'),'Month'[YearMonth] <=max('Month'[YearMonth])))

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[Tobias106]

@amitchandak 

Thank you!

But as I understand, I get with your formulas the cumulative sum till the max Date I choose ("<=max('Month'[YearMonth])))").

But I need January 2021 till December 2021, for example.

And after I that I want to create a cumulative line with JAN 2020 - DEC 2020, and so on.

Do you have an idea :-)?