MDX formula avg for last 35 days

Question

How can I average a Measure from my olap table by creating an MDX formula thru Excel's Set Manger (new set via MDX) or using Olap PivotTable extensions.

What I need is to get the average of a measurer called [Rental Count] for the last 35 days. my date dimension is [Rental Date].[Date] I also have a dimension called location, so I want to be able to look this up by location. some locations my have data for all 35 days, while others may have data for some of the 35 previous days.

Answer 1

This is straight mdx for a 35 day average:

AVG(
    TAIL(
      [Rental Date].[Date].[Date]
     ,35
    ) 
   ,[Measures].[Rental Count]   
) 

Excel-2013 has custom measures built in.

An alternative is a more generic solution for any measure. so in the following we could put this on columns, and then have various measures on rows (as long as this is created as a member of a hierarchy other than measures)

AVG(
    TAIL(
      [Rental Date].[Date].[Date]
     ,35
    ) 
) 

If you want this average to consider more than just the last 35 days in the date dimension and you will be including date on say Rows, then something similar to this is good:

WITH 
  MEMBER [Measures].[MovingAvg35Days] AS 
    Avg
    (
      [Date].[Calendar].CurrentMember.Lag(35) : [Date].[Calendar].CurrentMember
     ,[Measures].[Internet Sales Amount]
    ) 
SELECT 
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Internet Order Count]
   ,[Measures].[MovingAvg35Days]
  } ON 0
 ,Descendants
  (
    [Date].[Calendar].[Calendar Year].&[2007]
   ,[Date].[Calendar].[Date]
  ) ON 1
FROM [Adventure Works];