Moving date period filter in Excel 2010 pivot-table

2
votes

Is it possible to set some kind of filter for a moving date period?

For example one of the DB views I'm trying to replicate in my pivot-table has this filter :

DATEDIFF(day, dateColumn, GETDATE()) <= 90

So basically I always want to display the last 90 days of whataver data there is in the cube table. Is this possible?

1
excelssaspivot-table

1 Answers

2
votes

The answer to this question is here : http://blogs.socha.com/2010/05/sliding-date-ranges-with-excel-2010.html

Example for a moving period of 30 days :

  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

  8. Click OK

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