# KPI compared to last year

• ### Question

• I have a scenario where i have to develop KPI in cube. Scenario is simple, Goal is that this year sales should be 5% more then last year.

Measure is 'Sales Amount' and date is 'Calendar Hierarchy'

I believe my KPI value expression should be Measure.Sales Amount, but what should i write in my Goal and Status Expression?

Thanks in advance for the help1

Wednesday, July 20, 2011 5:03 PM

### Answers

• For Goal use Parallelperiod function:

parallelperiod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].currentmember)

For Status:

Case
When
KpiValue("Sales")/KpiGoal("Sales")<=0.95
Then -1
When
KpiValue("Sales")/KpiGoal("Sales")>=1.05
Then 1
Else 0
End

Marco Schreuder IN2BI DWH Deck

• Proposed as answer by Sunday, July 24, 2011 7:28 AM
• Marked as answer by Monday, July 25, 2011 10:07 AM
Thursday, July 21, 2011 6:58 AM
• Addition:

For Goal create calculated member SalesPrevYear:

with member measures.salesPrevYear as
(
parallelperiod(
[Date].[Calendar].[Calendar Year]
,1
,[Date].[Calendar].currentmember
)
,[Measures].[Sales]
)

and use [measures].[SalesPrevYear] in Goal expression

Marco Schreuder IN2BI DWH Deck

• Marked as answer by Monday, July 25, 2011 10:07 AM
Thursday, July 21, 2011 7:04 AM

### All replies

• For Goal use Parallelperiod function:

parallelperiod([Date].[Calendar].[Calendar Year],1,[Date].[Calendar].currentmember)

For Status:

Case
When
KpiValue("Sales")/KpiGoal("Sales")<=0.95
Then -1
When
KpiValue("Sales")/KpiGoal("Sales")>=1.05
Then 1
Else 0
End

Marco Schreuder IN2BI DWH Deck

• Proposed as answer by Sunday, July 24, 2011 7:28 AM
• Marked as answer by Monday, July 25, 2011 10:07 AM
Thursday, July 21, 2011 6:58 AM
• Addition:

For Goal create calculated member SalesPrevYear:

with member measures.salesPrevYear as
(
parallelperiod(
[Date].[Calendar].[Calendar Year]
,1
,[Date].[Calendar].currentmember
)
,[Measures].[Sales]
)

and use [measures].[SalesPrevYear] in Goal expression

Marco Schreuder IN2BI DWH Deck

• Marked as answer by Monday, July 25, 2011 10:07 AM
Thursday, July 21, 2011 7:04 AM
• I have one word for you in2bi. Simply Awesome!! and Thank You!
Thursday, July 21, 2011 3:50 PM