I need to create a calculated measure that sums all of the activity(minutes)
in my cube from the first date in the ActivityDate dimension until a given
time for example

8/16/2001

I'm using the following MDX :
SUM(PeriodsToDate([ActivityDate].[Std].[(All)] ,[ActivityDate].[Std].[Year
Number].[2001].&[24008].&[20010816]),[Measures].[Activity Minutes])

The problem is that I need to replace the hardcoded 8/16/2001 with the value
of a member property in the term dimension

I can do this using this mdx expression:
[Term].[Std].CurrentMember.Properties("TermAccessDateValue")

I'm having trouble putting the two pieces together I need to somehow convert
a string to the coresponding member in the ActivityDate Dimension

There seems like the must be a more elagant solution

Any ideas?

I worked this out using two calculated measures the first simply subtotals
the values:

SUM
(
PeriodsToDate
(
[ActivityDate].[Std].[(All)]
,strToMember
(
"[ActivityDate].[Std].&[" +
Cstr([Term].[Std].CurrentMember.Properties("TermAccessDateValue")) +"]"
)

)
,[Measures].[Activity Minutes]
)


The second performs the aggregations so that it rolls up nicly

SUM(Descendants([Term].[Std].CurrentMember,[Term Name]),[Measures].[Term
Access Minutes Sub Total])


This has caused performance to tank, I think this is due to having two
calculated measures for each snapshot.

I would like to be able to do this with a single calculated measure; but
this is just out of the grasp of my MDX skill level

any advice for refactoring this?


"Jason" wrote: