cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
J_L
Frequent Visitor

Calculating Moving Average based on slicer or filtered criteria

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. 

 

avg calc excel.png

 

Text below:

 

DATEDOWSALES
1st JulyMon22
2nd JulyTue12
3rd JulyWed31
4th JulyThu43
5th JulyFri25
6th JulySat62
7th JulySun23
8th JulyMon34
9th JulyTue24
10th JulyWed55
11th JulyThu32
12th JulyFri31
13th JulySat48
14th JulySun43
15th JulyMon23
16th JulyTue31
17th JulyWed35
18th JulyThu55
19th JulyFri34
20th JulySat63
21st JulySun14
22nd JulyMon43
23rd JulyTue35
24th JulyWed75
25th JulyThu46
26th JulyFri45
27th JulySat36
28th JulySun23
29th JulyMon19

 

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:

 

avg calc excel with DOW.png

 

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 Smiley Happy 

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

5 REPLIES 5
Ashish_Mathur
Super User
Super User

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

 

rolling_average.png

 

@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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors