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/200

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 mor

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

Mat

Matt--
I'm by far not an expert, but I think that the [Time].[Year] will take the
currentmember up to the year level and evaluate it. That is why you are
getting the whole year. You have to do like a YTD/QTD/MTD function for
Time.Currentmember.

-- Gary
"Looagerbalsak" <anonymous (AT) discussions (DOT) microsoft.com> wrote

is the weird part -
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.
is not selected)!!!!!!

----- Gary Murrish wrote: ----

Matt-
I'm by far not an expert, but I think that the [Time].[Year] will take th
currentmember up to the year level and evaluate it. That is why you ar
getting the whole year. You have to do like a YTD/QTD/MTD function fo
Time.Currentmember

-- Gar

Thanks Gary - I will try that - but it still does not explain why when I add another year drill down to the months that the second total shown is then corrct whilst the first is for the full year?!??!

Maybe if I do what you suggested it might change this