cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lcbdax Frequent Visitor
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

salesreset.PNG


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!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Dynamic Rolling Measure

@lcbdax ,

 

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.

lcbdax Frequent Visitor
Frequent Visitor

Re: Dynamic Rolling Measure


@v-yuta-msft wrote:

@lcbdax ,

 

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

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors