I am trying to write an MDX query that will return a 12-month history based
on the current date. I have found several resources explaining how to use
the NOW( ) function, but I am having a very difficult time following along.
I would appreciate it if someone could walk me through this in an easy to
understand manner. Google returned quite a bit of info about this topic,
but I had a very difficult time translating that into practice.

Right now, I am using the following query, which returns values for all the
entries in my time dimension. I need to limit this query so that it returns
only the previous 12 months from the date it is run:

SELECT
{[Time].[Time Hierarchy].[CalendarYear], [Time].[Time
Hierarchy].[CalendarMonth]}
ON COLUMNS,
{[Measures].[RevenueT12MA]}
on ROWS
FROM
[Day Activity]
WHERE
[Services].[Service Hierarchy].[Service Group].&[Day Activity].&[Older Adult
Day Care]

I understand that I can use StrToMember and NOW( ) to set the date ranges
that get returned, but I just can't figure out how to go about it. Here's
an example of what my time dimension strings would need to look like:

[Time].[2006].[Quarter 1].[3]

Thanks in advance,

-s

Here's an example for Adventure Works - but 2 is subtracted from the
Year, to shift to valid months (in 2004). Note that the Month attribute
has 2 key columns of Year and Month Number, which are used to select the
appropriate month:

Member [Measures].[ThisMonth] as
"[Date].[Calendar].[Month].&["
+ CStr(Year(Now())-2)+ "]&["
+ CStr(Month(Now())) + "]"
Set [Last12Months] as
LastPeriods(12,
StrToMember([Measures].[ThisMonth]).PrevMember)
select {[Measures].[Order Quantity]} on 0,
[Last12Months] on 1
from [Adventure Works]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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