Hello all,

I want to create a calculated member to get inventory of a specified
period, such as 1 month ago, 1 quarter ago, 1 year ago. I use AS2005
and have a date dimension with Date hierarchy as follows

Date
Year
Quarter
Month
Day

Assume the current date memeber is Decemeber 25, 2005

1. ParallelPeriod( [Date].[Date].[Year], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])
2. ParallelPeriod( [Date].[Date].[Quarter], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])
3. ParallelPeriod( [Date].[Date].[Month], 1,
ClosingPeriod([Date].[Date].[Day], [Date].[Date].[CurrentMember])

The 1st returns Decemeber 25, 2004, RIGHT
The 2nd returns the first day of the date dimension, WRONG
The 3rd returns the first day of the date dimension, WRONG

it's not clear to me why the 2nd and 3rd are not right. Could any
expert help me figure out the reason?

Thanks in advance.

Charles

Hi Charles,

Not sure how your Date dimension is structured, but you can compare it
to Adventure Works, where this works:

ParallelPeriod([Date].[Calendar].[Month], 1,
ClosingPeriod([Date].[Calendar].[Date],
[Date].[Calendar].[Date].[December 25, 2003])) on rows
from [Adventure Works]
The result on rows is [November 25, 2003]


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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