the last 12 months from my filtered date

[akoita92]

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                 

    )

)

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 !

[v-zhangti]

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.

[akoita92]

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.

[parry2k]

@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  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!

[akoita92]

Thanks @parry2k i'll try to do that.