I have a simple cube with one measure, "Activity Minutes".
The cube also has two "snapshot" calculated measures: MinutesAtStart and
MinutesAtEnd

The basic idea is that the snapshot measures should calculate the total
minutes for the the cells
at the given snapshot point

I have a standard Time Dimension "ActivityDate"

I also have a "period" dimension with several member properties that define
various "snapshot" dates
for the period

For example:

Period StartDate Endate
AY 2006 01/01/2006 06/01/2006
AY 2005 01/01/2005 06/01/2005

I would like to be able to Sum "Activity Minutes"
based on the start and end date snapshots using the calculated measures


I used the MDX PeriodsToDate Function in a calculated member to do this:

SUM(Descendants([Periods].CurrentMember,[Period name])
,
SUM
(
PeriodsToDate
(
[ActivityDate].[Std].[(All)]
,strToMember
(
"[ActivityDate].[Std].&[" +
Cstr([period].CurrentMember.Properties("StartDateValue")) +"]"
)

)
,[Measures].[Activity Minutes]
)

)

This seems to give accurate results most of the time; however when I use the
"ActivityDate"
in the columns the numbers don't seem add up correctly the values are
accurate for the "all" level but the values at the other levels (Year,Month
and Day) month are all the same value as the all level

ActivityMinutes MinutesAtEnd
All 108 29.00
2006 108 29.00
2005 29.00* WRONG this should be empty


Does anybody have any ideas on how to achieve this behavior

Based on the MDX for the calculated member, any current member on the
[ActivityDate] dimension is overriden by the "StartDateValue" property
on the [Periods] dimension.

Can you explain with more detail/examples how selections on these 2
dimensions should work with each other?


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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