Post reply

SSAS 2005 Trend KPI time period

  • December 2, 2011 at 6:03 am

    #144668

    I used this code in BIDS for my trend KPI for the month period.

    Case

    When IsEmpty(KPIGoal("Machine Efficiency")) Then Null

    When ([Time].[Month].PrevMember,[Measures].[Machine Efficiency]) < KPIValue("Machine Efficiency") Then 1

    When ([Time].[Month].PrevMember,[Measures].[Machine Efficiency]) = KPIValue("Machine Efficiency") Then 0

    Else -1

    End

    I've googled unsuccessfully for code that will handle any time period as my time dimension has date, month, quarter, and year.

    Can anyone point me to some code that will handle any time period?

  • December 19, 2011 at 12:32 pm

    #1423274

    Not sure what your time/date dimension looks like, but assuming you have a User Hierarchy called [Calendar Hierarchy] and you have designated it as a "time" dimension...you can use the ParallelPeriod function to get what you need.

    Case

    When IsEmpty(KPIGoal("Machine Efficiency")) Then Null

    When (

    ParallelPeriod(

    [Time].[Calendar Hierarchy].level

    ,1

    ,[Time].[Calendar Hierarchy].CurrentMember

    )

    ,[Measures].[Machine Efficiency]

    ) < KPIValue("Machine Efficiency") Then 1

    When (

    ParallelPeriod(

    [Time].[Calendar Hierarchy].level

    ,1

    ,[Time].[Calendar Hierarchy].CurrentMember

    )

    ,[Measures].[Machine Efficiency]

    ) = KPIValue("Machine Efficiency") Then 0

    Else -1

    End

    Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply