Calculating Moving Average based on slicer or filtered criteria

[J_L]

Hi team,

I'm still a newbie and hoping to get some DAX help for a measure I'm trying to add. I've searched many forums and youtube tutorials with no definitive luck! I've also tried using rolling average quick measure however I get ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.") in the calculation and I've ensured Auto Date/Time Time intelligence has been ticked in options for Data Load and also Marked my table as date. 

Basically, at each date, I wanted to calculate the moving average of sales for that date, say 7 days before and after so total 14 days. See my basic average excel calculation below for a mockup with expected results in column C. Cell C23 shows my excel formula for your info. 

Text below:

DATE	DOW	SALES
1st July	Mon	22
2nd July	Tue	12
3rd July	Wed	31
4th July	Thu	43
5th July	Fri	25
6th July	Sat	62
7th July	Sun	23
8th July	Mon	34
9th July	Tue	24
10th July	Wed	55
11th July	Thu	32
12th July	Fri	31
13th July	Sat	48
14th July	Sun	43
15th July	Mon	23
16th July	Tue	31
17th July	Wed	35
18th July	Thu	55
19th July	Fri	34
20th July	Sat	63
21st July	Sun	14
22nd July	Mon	43
23rd July	Tue	35
24th July	Wed	75
25th July	Thu	46
26th July	Fri	45
27th July	Sat	36
28th July	Sun	23
29th July	Mon	19

I've been using the solution below to calculate the Average 14 Days which works well provided by @parry2k , however I need to add a day of week (DOW) slicer or filter criteria that will only consider the day of week data/values selected for the calculation of the average.

Avg 14 Days =
VAR __startDate = CALCULATE( MIN( Table1[Date] ),  DATEADD( Table1[Date], -6, DAY ) )
VAR __endDate  = CALCULATE( MAX( Table1[Date] ),  DATEADD( Table1[Date], 7, DAY ) )
RETURN
CALCULATE( AVERAGE( Table1[SALES] ), DATESBETWEEN( Table1[Date], __startDate, __endDate ) )

For example:

Note: I've only averaged 3 days in the above example (plus and minus 1 day) just to show what I'm expecting.

Your help will be greatly apprecatied  

[Ashish_Mathur]

Hi,

The formula in the second screenshot is not only averaging 3 numbers - it is averaging all numbers from row 48 to 62.  What exactly do you want to do? - Average only 3 numbers or all numbers between rows 48 and 62?

[J_L]

Hi @Ashish_Mathur 

Thanks for getting back to me, yes, I would like to only average the 3 values (for example) in row 48, 55, 62 (i.e. the 3 Saturdays) when the DOW selector has been selected to Saturday.

Note:  as this is dummy data, I only chose  3 days, 1 day before and 1 day after because my original sample data was small but in the working version/data set there will be two or more years worth of data and I would like the formula to be flexible so that the measure can include say 6 Saturday's before and 7 Saturday's after. 

Hope this makes sense!

Cheers, JL

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[autodafe]

I believe there's a "Rolling Average" measure in the "Quick Measure" list....have you tried that?

[parry2k]

@J_L I think I know what you are looking for, I guess attached will go, in DAX I just did for 2 week (2 week going back and 2 week going forward), change it to  14 to switch weeks.