cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
andychu221 Frequent Visitor
Frequent Visitor

Minimum of rolling 12 month sum

Dear all,

 

I want to calculate the minimum value of rolling 12 month return for the following data set

Q1.JPG

 

I use the following code to calculate rolling 12 return and it can calculate the result correctly.

 

 

Rolling12M = 
CALCULATE(
SUM(DataBase[Return]),
DATESINPERIOD(
                DataBase[Date],
                LASTDATE(DataBase[Date]),
                -12,
                MONTH
)
)

 

 

However, i use MINX to calculate min value of rolling 12m return, but it return the min value of single month return

my code is as follows:

 

 

MaxDD = MinX(Database,[Rolling12M])

 

 

For example, I use [Rolling12M] code to calculate EUR Rolling12M return and the graph show minimum value is -24.39%

But when i use [MaxDD] code to calculate min value of [Rolling12M], it shows -7.39% (worst month return)

 

擷取.JPG

 

Does anyone can help me?

Thanks!

 

 

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
andychu221 Frequent Visitor
Frequent Visitor

Re: Minimum of rolling 12 month sum

 

 I use the following code to solve the problem temporary. However, the -120 is manual. Can i dynamic get the count between lastmonth and firstmonth? 

MaxDD = MINX(DATESINPERIOD(DataBase[Date],LASTDATE(DataBase[Date]), -120, MONTH),[Rolling12M])

 

擷取.JPG

andychu221 Frequent Visitor
Frequent Visitor

Re: Minimum of rolling 12 month sum

I use the following code to solve the issue. 

 

MonthCount = CALCULATE (COUNT ( Database[Date] ),FILTER (ALLSELECTED ( Database ),Database[Date]  <= MAX ( Database[Date] )))
4 REPLIES 4
andychu221 Frequent Visitor
Frequent Visitor

Re: Minimum of rolling 12 month sum

 

 I use the following code to solve the problem temporary. However, the -120 is manual. Can i dynamic get the count between lastmonth and firstmonth? 

MaxDD = MINX(DATESINPERIOD(DataBase[Date],LASTDATE(DataBase[Date]), -120, MONTH),[Rolling12M])

 

擷取.JPG

andychu221 Frequent Visitor
Frequent Visitor

Re: Minimum of rolling 12 month sum

I use the following code to solve the issue. 

 

MonthCount = CALCULATE (COUNT ( Database[Date] ),FILTER (ALLSELECTED ( Database ),Database[Date]  <= MAX ( Database[Date] )))
Community Support Team
Community Support Team

Re: Minimum of rolling 12 month sum

Hi @andychu221,

It's pleasant that your problem has been solved, could you please mark your reply as Answered?

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
VSchepens Regular Visitor
Regular Visitor

Re: Minimum of rolling 12 month sum

Hi,

 

I'm looking for exactly the same but than the minimum on month base and not on day base.

Any idea?

 

Kr,

 

Valérie