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.
Hi everyone !
May I implore ur help ? 😮
I'm on a probleme for days, I hope you can help me.
I have two columns :
01/01/2017 | 5
01/02/2017 | 7
01/03/2017 | 9
01/04/2017 | 6
01/05/2017 | 8
01/06/2017 | 2
01/07/2017 | 8
01/08/2017 | 6
01/09/2017 | 5
01/10/2017 | 2
01/11/2017 | 6
01/12/2017 | 9
01/01/2018 | 4
01/02/2018 | 4
01/03/2018 | 7
01/04/2018 | 8
01/05/2018 | 2
01/06/2018 | 8
01/07/2018 | 1
01/08/2018 | 8
01/09/2018 | 7
01/10/2018 | 4
01/11/2018 | 6
01/12/2018 | 3
One "Date" column, the other is "number"
I'd like to do a cumulative sum. There, no problem with the formula in DAX :
Cumule = CALCULATE ( SUM ( ESSAIS[QtyMonth] ); FILTER ( ALLEXCEPT ( ESSAIS; ESSAIS[Date].YEAR ); ESSAIS[Date] <= EARLIER ( ESSAIS[Date] ) ) )
But, I have an issue. I would like my sum to add the number on one rolling year.
For example :
[Jan2017+Feb2017+March2017+April2017+May2017+June2017+July2017+August2017+September2017+October2017+November2017+December2017]+(Jan2018-Jan2017)+(Feb2018-Jan2017-Feb2017)+(March2018-Jan2017-Feb2017-March2017) etc.
I hope you understand what I mean. If not, don't hesitate to ask for more information !
Thank you so much !
Tiffaine
Solved! Go to Solution.
Hi @Tiff,
Based on my test, you could refer to below formula:
Column = var a=[Date]-365 var b=CALCULATE(SUM(Table1[Running total]),FILTER('Table1','Table1'[Date]=a)) return [Running total]-b
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @Tiff,
Based on my test, you could refer to below formula:
Measure = CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2018))- 11*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=1))- 10*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=2))- 9*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=3))- 8*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=4))- 7*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=5))- 6*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=6))- 5*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=7))- 4*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=8))- 3*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=9))- 2*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=10))- 1*CALCULATE(SUM(Table1[Value]),FILTER('Table1','Table1'[Date].[Year]=2017&&MONTH('Table1'[Date])=11))
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Thank you @v-danhe-msft Daniel for your reply !
I tried you formula, but it doesn't work 😞
You can see below the column I want ("Cumulative Sum on rolling year").
As you can see it, the first year (January 2017 to December 2017) is a simple cumulative sum.
Then, in January 2018, it keeps the simple cumulative sum, but remove the cumulative sum until January 2017.
In February 2018, it keeps the simple cumulative sum, but remove the cumulative sum until February 2017.
[...]
In September 2018, it keeps the simple cumulative sum, but remove the cumulative sum until September 2017.
Do you know what I mean ?
I hope there is a way to do this...
Thanks a lot !
Tiffaine
Hi @Tiff,
Based on my test, you could refer to below formula:
Column = var a=[Date]-365 var b=CALCULATE(SUM(Table1[Running total]),FILTER('Table1','Table1'[Date]=a)) return [Running total]-b
Result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Thanks a lot ! It works !
I was beggining to think this wasn't possible in Power BI 🙂
Thank you again !
Tiffaine
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |