Dear all,

I have a time dimension and 6 measures. For each measure, I would like to
calculate the difference between two selected days/months/years. How can I do
this? Can I change the summary level to calculate difference, instead of
calculating sum of two selected periods? Thanks!

Polly

One way to achieve the same result would be to add a second time
dimension, which is essentially a copy of the first. This allows the
user to select another member, whose value can then be looked up and
subtracted (assuming you are only trying to select a pair of members):

http://groups-beta.google.com/group/...rver.olap/msg/
27564ddc49de36a1?hl=en
From: "Chris Webb [MS]"
Date: Thu, 6 Feb 2003 14:13:14 +0100

Subject: Re: Time range

Rather than let the user enter a date manually, you might want to do the
following instead: create two Time dimensions in your cube, one where
the
user can select the start of the time range and one where they select
the
end of the time range. You can then use calculated measures to return
the
aggregated values. Here's how you do this:


In your existing cube, call your Time dimension something like 'Start
Date'.
Then copy this dimension, and paste it into the same database; you will
get
a dialog asking you for a new name for this dimension, and you should
call
it something like 'End Date'. Next, create a virtual cube containing all
the
dimensions and measures from your regular cube. Since you can't add your
'End Date' dimension to this virtual cube in Analysis Manager (it isn't
connected with any cube), you need to use some DSO code to add it -
something like the following bit of VBScript is all you need:


Dim myserver
Dim db
Dim vcube
Dim fromdim
Set myserver=createobject("dso.ser*ver")
myserver.connect("MyServer")
Set db = myserver.mdstores("MyDatabase"*)
Set vcube = db.mdstores("MyVirtualCube")
Set fromdim = vcube.dimensions.addnew("End Date")
Vcube.update


You then have a virtual cube with two Time dimensions your users can use
to
set the start and end dates in their Time range. Finally you need to
create
some calculated measures that do the summing - the MDX would be
*something*
like:


SUM({
OPENINGPERIOD([START DATE].[LOWEST LEVEL], [START DATE].CURRENTMEMBER)
:
CLOSINGPERIOD([START DATE].[LOWEST LEVEL], LINKMEMBER([END
DATE].CURRENTMEMBER, [START DATE]))



}, VALIDMEASURE(MEASURES.[MY MEASURE]))


HTH,

Chris
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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