I'm pretty new to MDX and I'm trying to create a calculated member to
add to my cube based on the following dimension structure:


Year
Qtr
Month
Day (2005-10-03)
DateHour (2005-10-03 05:00)


What I need to do is create an 30 day moving average by hour of day.
So if the member I'm on is 2005-10-03 05:00, get the 05:00 hour of
every day for the last 30 days and average them, all other hours being
excluded.


Right now I have the following MDX:


Avg
(
[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember.Lag(30) :
[Session Dt].[Year - Qtr - DatePartMonth - Day -
DateHour].CurrentMember,
[Measures].[Not Registered]
)


The problem here is that it just gives me the last 30 hours, I need a
way to have the set being averaged be just the members with the same
hour of day as the member I'm on.

-------------------
Randy Knight
Sr. Database Engineer
match.com