Dynamic Rolling Measure

[Anonymous]

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

Thanks in advance!

[v-yuta-msft]

@Anonymous ,

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.

[Anonymous]

---

@v-yuta-msft wrote:

@Anonymous ,

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