cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Dynamic Rolling Measure

Hello,

I am trying to calculate a rolling sum of the previous 12M which re-calculates every 2 months.
I have achieved this using a helper column in the Calendar Table named 2M_1 and one measure having multiple nested ifs SalesReset2M

I am now trying to replicate the logic in SalesReset2M but with a simpler formula, but I am failing in making the value replicate itself, given month column being different to 2M_1.

How can I adapt my code below to get the same results as SalesReset2M ?

```SalesReset2M_1 =
var selectedmonth = if(HASONEVALUE('Date'[2M_1]),VALUES('Date'[2M_1]))
return

CALCULATE([SalesRolling],ALLEXCEPT('Date','Date'[2M_1]),'Date'[month]=selectedmonth,VALUES('Date'[MonthYear]))```

2 REPLIES 2
Community Support Team

## Re: Dynamic Rolling Measure

It's because in your filter, you have added this condition "'Date'[month] = selectedmonth", this will compare the values in the two columns [2M_1], [month]. To get the same results as SalesReset2M, just remove the condition like below:

```SalesReset2M_1 =
CALCULATE (
[SalesRolling],
ALLEXCEPT ( 'Date', 'Date'[2M_1] ),
VALUES ( 'Date'[MonthYear] )
)
```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

## Re: Dynamic Rolling Measure

@v-yuta-msft wrote:

It's because in your filter, you have added this condition "'Date'[month] = selectedmonth", this will compare the values in the two columns [2M_1], [month]. To get the same results as SalesReset2M, just remove the condition like below:

```SalesReset2M_1 =
CALCULATE (
[SalesRolling],
ALLEXCEPT ( 'Date', 'Date'[2M_1] ),
VALUES ( 'Date'[MonthYear] )
)```

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hey,

Thanks for the reply 🙂 but doing that formula will just calculate the normal Rolling Mesure and not reset every two months like I have in the last column, if you see, the same values repeats itself twice ,this is what I need to achieve.

Jan calculates and Feb gets also the same value
Mar calculates and April gets also the same value
so on and so forth

Announcements