I need some help getting the average value for a measure for parallel
months. I have a measure called Disbursements which is sporadically
populated on a monthly basis (goes back to Jan '05 right now. If I am
looking at Feb '09 I would like to see the average disbursements for
all prior February months ('08,'07,'06,'05). How could this be done in
AS2000?

Scott

Hi Scott,

Here is a sample Foodmart query, which depends on the regularity of the
Time hierarchy, ie. that each year has the same structure under it. This
only works for levels below year (month and quarter in this case).

'Avg([Time].[Year].Members
- {{[Time].CurrentMember} as TC},
([Measures].[Units Shipped],
Cousin(TC.Item(0).Item(0), [Time].CurrentMember)))'
select {[Measures].[Units Shipped],
[Measures].[AvgYearlyShipped]} on 0,
[Time].[Month].Members on 1
from Warehouse

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***