KPI that compares the growth over years

Hi there,
I'm new to SSAS and MDX. Creating my first dimensions and cubes went fairly fine. But now I hang on KPI.
I'm trying to create a KPI which shows the growth of bookings comparing one year to the previous year.
I want to set a fiterexpression for the time-dimension (e.g. YearString equals 1998). If a filter is set, it should show the bookings for that year ([Measures].[Bookings]) as value and the bookings of the previous year as goal.
My problem is, that I cannot access the selected YearString, to get the bookings of the previous year. I tried the function parallelPeriod() and also a self-created Calculation, but it seems that [dimension time].[hierachy].CurrentMember is always [dimension time].[hierachy].[(All)].

My data structure is as followed:

Fact-table: FactBookings
SeasonID (FK)
YearID (FK)
AgencyID (FK)
CountryID (FK)
RegionID (FK)
IncomeID (FK)
AgeID (FK)
AgencyID (FK)
Bookings

Dimensions:
Income, Age, Agency:
[dimensionname]ID
[dimensionname]String

Dimension geography (having a hierarchy):
RegionID
CountryID
RegionString
CountryString
primary key: RegionID+CountryID

Dimension time (having a hierarchy):
YearID
SeasonID
YearString
Season String
primary key: YearID+SeasonID

Is there a way to access the filterexpressions for a KPI, or does anyone has an idea, how a calculation might has to be, which shows the value of the previous year compared to the actual year?

It would be very nice, if anybody could give me an idea how to create such a KPI.

Regards,
Maik.

First, you need to create the calculated member for the PreviousYear. You can use either Time Intelligence Wizard, you use formulas from the following blog post: http://www.sqljunkies.com/WebLog/mosha/archive/2006/10/25/time_calculations_parallelperiod.aspx

After that, you can enter the expression for KPI as (Time.PreviousYear, Measures.Booking) and it should work fine.

HTH,

Mosha (http://www.mosha.com/msolap)

|||Thank for your answer.
I've tried it for hours.
Now I have a dimension [Time Calculations]
with a calculated member:
CREATE [Time Calculations].[Prior Year] =
(ParallelPeriod([Dimension Time].[YearString - SeasonString].[YearString]), [Time Calculations].[Current Period]);

But for my KPI something like
([Time Calculations].[Prior Year], [Measures].[Bookings])
as the goal-expression wasn't working.

Finally, after turning IsAggregatable to false for YearString in dimension time, I was able to select a year at the KPI-Browser and the goal-expression showed up the measure-value of the previous year. Yeah! .
But I'm still not completely happy. Because of turning IsAggregatable to false, the measure in the Cube-Browser is fixed either to the first year or to one year specified as a filter.
At this point I'm unable to summarise/aggregate the bookings over all time or more than one year.

Any idea for that?

BTW: nice blog Mosha .|||You shouldn't have had to set IsAggregatable to False on Year attribute. Just remember - when it is aggregatable - you will have All level, so the current member inside your Time dimension will be All Years, so you will need to manually position to specific year in order to see a previous year.