DAX rolling average

Question

Hi,

I was trying to make a rolling average of last 4 weeks or something similar.

Tried several ways but no good result.

-Lets say today its feb 13th and I´d like to rollback from feb 11th (monday of current week)  to 21th of jan (4 weeks before).

-Sum it all and then /4 (divide by 4) for average.

This must be show for every week show in the query.

my trials:

this seems not valid, as it should show per month. There is no DAY/week in parallelperiod()

TAM 4 semanas:=CALCULATE(
    IF([SalesAmount]<>0;[SalesAmount];BLANK());
            DATESBETWEEN(
                 Dates[PK_Date];
                FIRSTDATE(PARALLELPERIOD(Dates[PK_Date]; -1; MONTH));
                LASTDATE(PARALLELPERIOD(Dates[PK_Date]; 0; MONTH))
            );ALL(Fechas)
)

this seems not working:

TAM prueba 2:=AVERAGEX(
SUMMARIZE(
DATESINPERIOD(Dates[PK_Date];FIRSTDATE(Dates[PK_Date]);-28;DAY)
  ;Dates[PK_Date]
  ; "TAMPRUEBA2"
  ;[SalesAmount]
       )
;[TAMPRUEBA2]
)



If someone can give me an approach to what Im looking for it would be nice.

Answer 1

Here's one way to accomplish this.  Add a "WeekStartDate" to your date table.  If you don't have these defined in your DW (or in SQL) and have to do it in DAX, this will work:

DayOfWeekNumber =WEEKDAY([FullDate],2)
WeekStartDate =DATEADD(DimDate[FullDate],(-1 * DimDate[DayOfWeekNumber]) + 1,DAY)

With that, you can roll back 28 days from each week start and get the trailing 28 sum of sales, and then divide by 4.

=CALCULATE([SumSales]            
                      ,DATESINPERIOD(DimDate[FullDate]
                                   ,MIN(DimDate[WeekStartDate])
                                   ,-28
                                   ,DAY
                                  )
                    )
   /  4

Working as expected in my test model.  Let me know if that helps. 

You could also make this a bit safer by doing a distinct count of weekstartdates in the denominator.  That would prevent you from producing an incorrect average at the beginning of your sales data (when you don't have a full 4 weeks).

---

Brent Greenwood, MS, MCITP, CBIP
// Please mark correct answers and helpful posts //
http://brentgreenwood.blogspot.com

Answer 2

Hi

I still have a problem with this calculation

The thing is that the data seems "moved" from the week that It should be displaying for 2 weeks.

So if 813 is expected for week 5 it is shown in week 7.

I tried another calculation and this seems working but the data is displayed 1 week later. :

testmeasure:=CALCULATE(
                                               SUM([Sales Amount Actual])/4; DATESINPERIOD(Dates[PK_Date];FIRSTDATE(Dates[PK_Date]);-28;DAY)
                                           )

Answer 3

If any1 can help I would really appreciate.

thanks!

Answer 4

Can you post a screenshot of what you're seeing?

I don't see an issue in the formula.  Only thing you could add would be another filter for ALL(Dates) if you want to be able to slice at higher levels in the date hierarchy.

Also, easiest way to troubleshoot this is to put your daily sales next to your trailing sales like this.  Take the average out of the equation and make sure your date range is correct. 

You can see it tops out at 28 (SaleAmount = 1 per day in my example).  Here's a link to the file if you want to check it out.

---

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com

Answer 5

Thanks for your help!

I was trying to dl the file but when I go into the powerpivot it tells me its built using newer version of excel (i´m on 2010)

Can you try by setting weeks in rows?

I just re-checked and I´d like to ask te consultant again I don´t see my calculation is wrong now.

I´ll let you know.

Answer 6

Hi

Seems like not working properly. I finally made a change and calculated  a column with the last day of current week by doing:

lastWeekDate=DATEADD(Dates[PK_Date];(-1*[WeekDayCalc])+7;DAY)

This is the result I get. Left grey is weeks. It shows OK, but when we go up to week 2 results are coming incorrect. It shold show:

wk2: 2169.92

wk1: 2085.42

and previous year is also incorrect.

my calculation its like:

4 wks avg:=CALCULATE([total sales];
                                                DATESINPERIOD(Dates[pk_date];MAX(Dates[LastweekDate]);-27;DAY)
                                               )/4

Answer 7

Hi agai Brent,

I noticed the following:

when we rollback 28 days we are not rolling weeks, just days, so when you query the model with dim.date and week it doesn´t show proper value, I think thats the explanation.

I don´t have any clue how to rollback in weeks, not in days.

Answer 8

Whether you want week start date like your original question, or now week end date, the formula is straightforward.  Suggest you store it in your date table as a calculated column.  With that, rolling back 28 days will always give you a full 4 weeks (assuming 7 day weeks).  Not sure why you're having so many issues.

Like I mentioned before, the easiest way to troubleshoot this is to put daily sales next to the trailing period sales in a pivot in Excel.  Then you can very easily see if it's working and exactly what days are included in the trailing period.

Also, if you want some more examples on the topic, suggest you check out Marco Russo's blog post here on week-based time intelligence.  Similar pattern to this post Paul te Braak wrote a while back on dealing with custom 445 calendars.  Both use a similiar approach of manually filtering the Date table to make sure higher levels of the hierarchy are equal (same year, same month, etc.).  If you're dead set on using week number instead of days, you can easily copy their approach if you populate your Date table properly.

Hope that helps.

---

Brent Greenwood, MS, MCITP, CBIP // Please mark correct answers and helpful posts // http://brentgreenwood.blogspot.com