Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
akoita92
Frequent Visitor

the last 12 months from my filtered date

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.

Measure =
CALCULATE (
   SUM(f_histo_production[valo_prod]),
   DATESINPERIOD (
      f_histo_production[date],          
      MAX ( f_histo_production[date] ), 
      -12,                   
      MONTH                 
    )
)

 

akoita92_0-1660946172552.png

 

 

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 !

 

4 REPLIES 4
v-zhangti
Community Support
Community Support

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

vzhangti_0-1661237283821.pngvzhangti_1-1661237305331.png

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

akoita92_0-1661240138610.png

 

 

this is what i would like to have

akoita92_1-1661240160982.png

 

 

I thank you for taking the time to help me.

parry2k
Super User
Super User

@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

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

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.

Thanks @parry2k i'll try to do that. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.