KPI’s (Key Performance Indicators) in Analysis Services are a great way to show visually if a measure is trending in right direction based on previous levels. If you want to show a measure has increased or decreased since last month, this can be accomplished by using the MDX function PrevMember. You want the trend to show at the month level only in excel. This way the users are not confused by extra trend graphics. This example will be using the adventure works cube.

Create a KPI in the cube and select the measure you want to compare.

The Goal will be a case statement to determine if the user is looking at the month level. If the user is not at the month level the goal will be null. If the user is at the month level then the goal will be a tuple of the previous month.

Case When ([Due Date].[Date].CurrentMember.level is [Due Date].[Date].[Month Name]) then

([Due Date].[Date].prevmember,[Measures].[Sales Amount Internet])

Else Null

End

The trend will check for the goal value, if the goal is null then the trend will be null. This ensures the trend only shows on the month level. If the goal is not null then the tuple for the previous month is compared to the KPI Value. Based on this comparison you will return a 1,0, or -1.

Case

When Isempty(KPIGoal(“Monthly Internet Sales”)) Then Null

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) <

KPIValue(“Monthly Internet Sales”) Then 1

When ([Due Date].[Date].prevmember, [Measures].[Sales Amount Internet]) =

KPIValue(“Monthly Internet Sales”) Then 0

Else -1

End

This is what it looks like in excel.