Advanced Cube Design - Professional SQL Server Analysis Services 2005 with MDX - page 413

The above expression uses the Case MDX statement available for
use with Analysis Services 2005. In addition, you now have a set of
MDX functions to use with KPI metric values. In the previous MDX
expression, the KpiValue function retrieves the value of Sales Reven-
ue KPI, and the KpiGoal function retrieves the goal value of Sales
Revenue KPI. More precisely, the KpiValue function is a member
function that returns a calculated measure from the Measures dimen-
sion. By using KPI functions, you can avoid a lot of typing if your
value or goal expression is complex. This Status expression will re-
turn one of three discrete values — 1 if revenue exceeds 95% of
goal, 0 if revenue is between 85%-95% of goal, and -1 if revenue is
below 85% of goal.
10. Choose the default indicator (Standard Arrow) for Trend indicator.
Type the following MDX expression in the Trend Expression text box.
This expression compares current KPI values with last year's values
from the same time period to calculate the trend of the KPI.
Case
When (
KpiValue ( "Sales Revenue KPI" )-
(
KpiValue ( "Sales Revenue KPI" ),
ParallelPeriod
(
[Date].[Fiscal].[Fiscal Year],
1,
[Date].[Fiscal].CurrentMember
)
)) /
(
KpiValue ( "Sales Revenue KPI" ),
ParallelPeriod
(
[Date].[Fiscal].[Fiscal Year],
1,
[Date].[Fiscal].CurrentMember
)
)