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 there,
I come to solicit the forum because I am confronted with a problem concerning the calculation of a measurement.
I thank you in advance.
I have the following code which calculates the sums of the last 12 rolling months.
I would like to be able to filter on a date and have the last 12 months from my filtered date.
with my current code if I filter on the date, it just gives me the date on which I filtered, which is normal.
What can I do to get the desired result.
if you need more information do not hesitate to come to me.
Thanks !
Hi, @akoita92
You can try the following methods.
Cumulative 12 month =
Var N1=SUMMARIZE(FILTER(ALL('Table'),[Date]<=SELECTEDVALUE('Table'[Date])),[Date],"Sum",SUM('Table'[valo_prod]))
Var N2=TOPN(12,N1,[Date],DESC)
Var Cumulative12month=SUMX(N2,[Sum])
return
Cumulative12month
Is this the result you expect? Select a date to output the sum of the past 12 months cumulatively.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello @v-zhangti
Indeed, what I'm looking for is the cumulative sum of the last 12 months. your code is working fine.
However, I would like to have the following result :
As in your screenshot, you have filtered on "7/31/2022", I would like to display the other dates where each date represents the cumulative of the last 12 months compared to the date.
Here's what you did
this is what i would like to have
I thank you for taking the time to help me.
@akoita92 for any time intelligence calculation, it is a best practice to use a calendar table and that is the reason your code is not working, you should add a calendar table, and use DATESINPERIOD function with a column from this new table and everything will work, learn more about importance of calendar table and time intelligence function, check my youtube playlist on this. https://youtube.com/playlist?list=PLiYSIjh4cEx2FkuGkngYruS0wm8MYFsoi
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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 |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
138 | |
107 | |
104 | |
82 | |
63 |