Calculating a Moving Average in PowerPivot


Two weeks ago I promised to talk about how to generate a moving average in PowerPivot, but then last week I got sidetracked by telling you about a cool way to display YouTube videos on your SharePoint pages using a web part found on CodePlex that some of my work team members found.  It was so easy to implement, I just had to share it with you all.

However, returning back to the topic of calculating a moving average, the first question might be what is a moving average and then why would you want to use one.  A moving average is simply the sum of two or more time-dependent values in which the sum is then divided by the number of values used.  For example, if I was talking about stock prices, I may want to use something like a 7-day moving average to dampen the effect of individual day spikes or drops in the stock price that are not indicative of the overall stock trend.  (Some long term investors use even longer period moving averages.)  That does not mean that if a stock plummets or soars that I would sit back until the moving average tells me to act.  Any good stock investor will tell you there are many other factors both internal and external to a company that could force your hand to sell or buy any particular stock.  But the point is, and this is the answer to the second question, a moving average dampens randomness so I can more easily see the overall pattern of the numbers that I am tracking.

Ok, so suppose I work for Contoso and wanted to know whether sales are rising, falling or generally flat.  If I look at daily sales, the numbers are likely to fluctuate up and down in no particular pattern preventing me from spotting an overall trend.  The following figure shows Contoso daily Contoso sales over a 3 month period during the summer of 2008.  I chose to show the data as a chart to help show how sales fluctuate by day revealing information that I might not be able to see as easily had I created a table of the same values.

Of course, I could chart an entire year or more, but to see individual days, I would have to widen the chart substantially.  However, even with this smaller time period, I can see that the sales fluctuate quite nicely.  But I might ask are sales increasing, decreasing or staying the same.  If I have a good eye, I might say that sales peak toward the end of July and then fall back a little as the chart moves into August.  But that is not as obvious as the fact that there is a great deal of daily fluctuation.

So how can I visually display trends?  With Moving Average Sales.  Now for the purpose of this illustration, I’m going to create a four-day moving average, but honestly, there is no one right number of periods in a moving average.  In fact, I should experiment with different time periods to see which time period allows me to spot not only overall trends, but also in this case where I am displaying store sales, at seasonal changes.

I already know that if I display data by day, I can use the following formula to calculate the daily sales of just our ‘store’ channel. (Yes, I could simply use [SalesAmount] and apply a channel slicer to use only ‘store’ sales, but let’s stick with the example.)

StoreSales:=CALCULATE(SUM([SalesAmount]),DimChannel[ChannelName]=”Store”)

I can then use this calculated measure to calculate the previous day’s sales for any day by creating the following measure.

StoreSales1DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -1, day))

You might be able to guess that the formula for calculating sales for two days ago and three days ago respectively are:

StoreSales2DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -2, day))

StoreSales3DayAgo:=CALCULATE([StoreSales], DATEADD(DimDate[DateKey], -3, day))

With these four values calculated for each day, I can calculate the sum of these values and divide by 4 to get a 4 day moving average using the following calculated value:

FourDayAverage:=([StoreSales] + [StoreSales1DayAgo] + [StoreSales2DayAgo] + [StoreSales3DayAgo]) / 4.0

Now if I switch back to my chart page, I should see that Excel updates the field list to include the new calculated measures.  If I then add the field [FourDayAverage] to the Values box creating a second series in the chart, I now have both the actual daily sales and the four day moving average displayed in the same chart.  The only problem is, I would also want to change the chart format to display the daily sales (my first data series) as columns and my moving average (my second data series) as a line.  When I right click on the chart and select Change Chart Type, I can select Combo as the chart type as shown in the following figure.  In this case, the Clustered Column – Line chart is exactly what I want.  Because I added the moving average series to the Values area last, it becomes by default the Line and all other data series appear as clustered columns.  Since I only have one value for each day, the chart shows an individual column per day.

If I had entered my data series into the Values area in the wrong order, I could simply use this dialog to select the chart type for each series.  When I click OK in this dialog, my chart now looks like the following which more clearly shows the more of the overall trend and less daily fluctuation.

But wait, is there an easier way to do this? Why yes there is.  But to learn how to do that, you will have to wait until next week.

C’ya next time.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s