Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
@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 :)?
Hi,
Please share some data (in format that can be pasted in an MS Excel file) and show the expected result.
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.
@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
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 :-)?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
96 | |
75 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |