Sliding Date Ranges with Excel 2010 Pivot Tables

Using an Excel PivotTable to retrieve and graph data from the TFS cube is really easy. And it’s pretty easy to setup date filters for specific date ranges. But what if you want the date range to be a sliding range, such as the past 4 weeks? The answer is with a named set.

If you’re using Excel 2010, there is now a dialog box that allows you to create named sets. Describing a date range that is from today going back N weeks requires a little snippet of MDX (Multi-Dimensional eXpression language). I’ll present the snippet that you need and I’ll explain how to change the number of weeks, days, etc.

Create a Named Set

Here is how to create a named date range set:

1. Select a cell inside a pivot table bound to the cube so that the PivotTable tools are available
2. Click the Options tab on the ribbon under the PivotTable Tools section:
   
3. Click the Fields, Items & Sets drop-down in the Calculations section of this ribbon tab
4. Click Manage Sets… in the drop-down
5. Click New… and then Create Set using MDX…
   
6. Enter a name for this set in the Set name text box
7. Enter the MDX expression that defines the date range (see below)
8. Click OK

Using a Named Set

Once you’ve defined a named set, it will appear in the PivotTable field list. Where it appears will depend on how you’ve defined in the named set. The sets we’ll define in the next section are all based on the Date dimension, so they’ll appear under that dimension, something like this:

You can now use this named set wherever you would use any of the Date dimension’s attributes.

Defining the Named Set

Here is where we get into the actual MDX used to define the named set (that you would enter in step 7 above).

Last 30 Days

For the first example, we’ll create a named set that returns all of the days during the last 30 days. Here is the MDX:

Filter(
    [Date].[Date].[Date],
    [Date].[Date].CurrentMember.Member_Value < Now()
    AND [Date].[Date].CurrentMember.Member_Value >= DateAdd("d", -30, VBA![Date]())
)

Yikes, you say? Well, you can use this without understanding most of what’s inside. The two important pieces are inside the DateAdd function: “d” and –30. The “d” says that we want to add days, and –30 says to go back 30 days. So it includes the last 30 days, plus today (for a total of 31 days).

Last 4 Weeks

Now let’s say that instead of every day, you just want to see the week ending summary. Here is the expression you can use in that case:

Filter(
    [Date].[Year - Week - Date Hierarchy].[Week],
    [Date].[Year - Week - Date Hierarchy].CurrentMember.Member_Value < Now()
    AND [Date].[Year - Week - Date Hierarchy].CurrentMember.Member_Value >=
        DateAdd("ww", -4, VBA![Date]())
)

Here we’ve changed the interval to week (ww) so we get the last four weeks.