locked
KPI compared to last year RRS feed

  • 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 in2bi Sunday, July 24, 2011 7:28 AM
    • Marked as answer by Challen Fu 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 Challen Fu 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 in2bi Sunday, July 24, 2011 7:28 AM
    • Marked as answer by Challen Fu 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 Challen Fu 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