Rolling Averages in Excel

In a previous post, I talked about how to add named ranges to an Excel report that will show, for example, the last two weeks of data from a TFS cube. Recently someone asked how we added the rolling average to a pivot table. In this post I’ll provide you with the information on how to do that.

First, if you open the Bug Trends Excel workbook that is part of the TFS 2010 dashboards and click on the pivot table, you’ll see that there is a rolling average value shown in the PivotTable Field List:

It turns out that Excel doesn’t have a way for you to manage calculated fields like this. Instead, you have to use a little bit of VBA code. You’ll need to do the following:

• Open Excel’s VBA editor
• Enter the code below
• Place the cursor inside the code and press F5 to run the code
• Delete the code

The final step of deleting the code is required because workbooks uploaded to Excel Services are not allowed to contain any VBA code. The code below will create the calculated measure and add it to the first pivot table in the workbook.

Sub AddCalculatedMeasureRollingAvg()
    Dim pvt As PivotTable
    Dim measureName As String
    Dim measureFormula As String

    Set pvt = Sheets(1).PivotTables(1)
    measureName = "[Measures].[Rolling Average]"
    measureFormula = "Avg([Date].[Date].CurrentMember.Lag(6): " + _
        "[Date].[Date].CurrentMember,CoalesceEmpty([Measures].[State Change Count], 0))"

    
    On Error Resume Next
        Dim member As CalculatedMember
        Set member = pvt.CalculatedMembers(measureName)
        member.Delete
    On Error GoTo 0
    
    pvt.CalculatedMembers.Add Name:=measureName, Formula:=measureFormula, _
        Type:=xlCalculatedMember
    pvt.ViewCalculatedMembers = True
End Sub

Near the top of this code snippet is the definition of the rolling average, which uses a snippet of MDX code. There are two pieces of this that you might change:

• Lag(6): You can change this number to change the length of the rolling average. The value of 6 gives you a 7-day rolling average (today back to six days before today).
• State Change Count: Near the end of the string is the name of the measure that will be averaged. Here we’re averaging the number of times bugs changed state. You could change this to Work Item Count, for example, if you wanted to show a rolling average of the total number of bugs.