Rolling 4W Average by Store

[MrAnalytics]

First time poster here - thank you for all the people that take the time to respond!

I am trying to execute (what should be) a very simple average formula but I can't seem to get the right output. I need to have a rolling 4 week average calculating for each week and each store in my table. My table looks like this:

The table name can be 'WeeklySales' in this example. [WeekUID] is a unique week field that I am using starting Jan 2019 and it increases by 1 every week following Jan 2019 until infinity so that I can run rolling calculations without worrying about week numbers resettting in the new year.

Simply put, I would like every week for every store to show a 4 week rolling average. As an example, week 199 would be an average of all sales from week 196 to week 199. Week 174 would be an average of all sales from weeks 171 to 174.

I have tried the following DAX formula but it is not working and I'm sure that there is an obvious reason why:

Sales 4wravg =

var salesavg = average('WeeklySales'[sales])

var weeksstart = max('WeeklySales'[WeekUID])

var weeksend = weeksstart - 3

RETURN

Calculate(salesavg,weekstart >= weeksend, weeksstart <= weekstart)

The logic is that if I am calucalting for week 199 in the table, this formula would read Calculate(Saleavg where the row's week >= 196 and is <= to week 199)

This doesn't work when I drop it into a table but I would like to be able to see it as:

I'm certain that this has to do with the second filter in the formula as well as the need for "KEEPFILTERS" or "ALLSELECTED" please help 🙂

[v-yueyunzh-msft · Accepted solution]

 Hi , @MrAnalytics 

According to your description, you want to  calculate the "Rolling 4W Average by Store" with the column [WeekUID].
For your need, you need to calucate the four week , so i think the number is like "1-4,5-8,9-12 ......". Right?

Here are the steps you can refer to :

(1)This is my test data :

(2)We can create a measure like this:

Measure = var _cuurnt_store= SELECTEDVALUE('WeeklySales'[StoreNo])
var _current_weekID= SELECTEDVALUE('WeeklySales'[WeekUID]) 
var _test = IF( MOD(_current_weekID,4)=0 , ROUNDDOWN( DIVIDE(_current_weekID,4 )   ,0)-1,ROUNDDOWN( DIVIDE(_current_weekID,4 )   ,0))
var _first_weekID =_test * 4+1
var _end_weekID = (_test  +1) * 4
var _t = FILTER(  ALL( 'WeeklySales') , 'WeeklySales'[StoreNo]=_cuurnt_store &&  'WeeklySales'[WeekUID] >= _first_weekID && 'WeeklySales'[WeekUID] <= _end_weekID)
return 
AVERAGEX(_t,[Sales])

 (3)Then we can put the measure on the visual and we will meet your need :

Best Regards,

Aniya Zhang

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

[HoangHugo]

yes, you need to remove "WeekUID" filter in your matrix to return right average

try this

Sales 4wravg =

var salesavg = average('WeeklySales'[sales])

var weeksstart = max('WeeklySales'[WeekUID])

var weeksend = weeksstart - 3

RETURN

Calculate(salesavg,ALLSELECTED('WeeklySales'[WeekUID]),weekstart >= weeksend, weeksstart <= weekstart)