cancel
Showing results for
Did you mean:
andychu221 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 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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
andychu221 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])` andychu221 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

## 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])` andychu221 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

## Re: Minimum of rolling 12 month sum

Hi @andychu221,

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

## 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