Calculated Measure (Previous Act Value) Problem

G'Day all,

I am having trouble with a calculation that on the surface seems to be working until it is opened up in excel.

The measure calculates the Actual Value as of last year and is as follows - ([Measures].[Act Val],ParallelPeriod([Time].[Year],1))

This works fine until I look at the totals within EXCEL 2002/2003

1)If I choose to view a whole year then the total shows me a whole year. That is fine and as expected.

2)If I choose part of the year the total shows me the whole year. This is wrong and am wondering if it is an Excel probem or my calculation? But here is the weird part -

3)If I choose another year and select the same months as I have already chosen (ie Year 20023 Jan, Feb and Year 2003 Jan and Feb) then the total for the year that is already selected works!!(shows only the total of the two months - whereas the extra year I have selected (remember - Jan Feb only) shows me a total for the whole year.

WAIT - There's more

4)If I add another month to the 2002 selection (ie add March) then the total for 2003 (Jan Feb only) includes the March value also (even though it is not selected)!!!!!!

Any Ideas??

Does it make sense??

Thanks

MattWell...

ParallelPeriod

Is a udf someone wrote...

How are you connecting to sql server?

Are you connecting to sql server?

Sorry...should've looked first...

I have GOT to start playing with that stuff...never had any real need...|||No, ParallelPeriod is an Analysis Services function. I don't know anything more about it than that.

From the Holy Book:

ParallelPeriod
Returns a member from a prior period in the same relative position as a specified member.

Syntax
ParallelPeriod([Level[, Numeric Expression[, Member]]])

Remarks
This function is similar to the Cousin function, but is more closely related to time series. It takes the ancestor of Member at Level (call it ancestor); then it takes the sibling of ancestor that lags by Numeric Expression, and returns the parallel period of Member among the descendants of that sibling.|||You should it's a powerful addition to SQL Server - only sometimes you can get lost in translation (so to speak) - the MDX statements (language for writing the calcs) sometimes take a while to understand. Hopefully you take a crash course and help me with my problem.