How to determine a moving average + using it to establish trends

[Anonymous]

Hi community, 

Pretty new to PBI but I do have a few weeks of experience using power query and creating visualizations. 

I am attempting to determine a moving average for multiple sets of data with 250k+ rows. I need the moving average to determine an established average along any time period, using the slicer. Then, I need to use that moving average to determine a basis for a "normal range" say, +/- 20% of the average, at any point in time.

My ultimate goal is to have an idetifier, or flag, that would let me determine at what points the data is faulting at places outside of the normal average. 

Below I have posted the visualization I am using, ideally it would show a hatched area that would show the reader when the data is within/outside the specified range. 

As I said I have multiple sets of monitors that I need to model, so this would need to be for each monitor, preferably with the least amount of queries as possible, due to the large amount of data. 

Thanks!!!!

[v-diye-msft · Accepted solution]

Hi @Anonymous ,

Plz kindly refer to this post: https://community.powerbi.com/t5/Desktop/Moving-Average/m-p/43041

I think you can create the average measure by the solution provided, and then determine the range +/- 20% of the average using measure some like below one: (To filter the points that is faulting at places outside of the normal average) 

Measure :=
CALCULATE (
Sum(data[qty]),
FILTER (
ALL ( Calendar ),
Calendar[MonthID] >= [Average] *1.2
|| Calendar[MonthID] <= [Average]*0.8
)
)

[Anonymous]

If it helps, here is a picture of the data for each monitor. 

The data is comprised by date, and then flow monitor, and then by pressure as shown below. The failure column lists any times the pressure is below a certain threshold, however I need it to be a little more specific than that for this project. 

Thanks!

[v-diye-msft]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution. if not, please share more details about your question, we'd like to provide further support. thanks!
If you've fixed by your own, it would be much appreciated if you can share your solution here.