cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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

1 ACCEPTED SOLUTION
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
5 REPLIES 5
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
Frequent Visitor

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

Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors