Minimum of rolling 12 month sum

[andychu221]

Dear all,

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

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)

Does anyone can help me?

Thanks!

[andychu221 · Accepted solution]

 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])

[andychu221 · Accepted solution]

 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])

[Anonymous]

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

[andychu221 · Accepted solution]

I use the following code to solve the issue. 

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

[v-danhe-msft]

Hi @andychu221,

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

Regards,

Daniel He