locked
SSAS - Calculations Current YTD vs Prior YTD Same Periods RRS feed

  • Question

  • I have a cube that has a sales fact table and a time dimension (Fiscal
    Year).

    I am trying to determine the best way to use a calculation in SSAS to
    calculate the year over year growth for teh current fiscal year to
    date vs the previous fiscal year to date.  My dimension has a year,
    week hierarchy and my fact table is by week.

    The Year over Year Growth template calculates using the previous full
    sales years amount vs the currenty FYTD amount.

    For example, if I am in the 10th week of 2009, I want to compare the
    total amount for weeks 1-10 of 2009 vs the first 10 weeks of 2008.

    Can anyone help?

    Monday, August 3, 2009 5:08 PM

Answers

  • Hi Margni,

     

    If I understand correctly, try to create a calculated measure on [Adventure Works], with expression:

    IIF(

           SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

           )=0

         , "",

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/

          SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          ) - 1

       )

     

    It will compare the total value in current period with lat period. You can also test the results with below code:

     

    WITH MEMBER Measures.[Change Amount Rate] AS

       IIF(

           SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

           )=0

         , "",

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/

          SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          ) - 1

       ), FORMAT_STRING = '0%' 

    -------------------------   [Current Periods]

        MEMBER Measures.[Current Periods] AS 

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])

    -------------------------   [last Periods]

        MEMBER Measures.[last Periods] AS 

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          )

       

    SELECT {Measures.[Current Periods],  Measures.[last Periods], Measures.[Change Amount Rate]} ON 0,

    [Date].[Fiscal].[Month].Members ON 1

    FROM [Adventure Works]

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Thursday, August 13, 2009 10:01 AM
    Wednesday, August 5, 2009 6:21 AM

All replies

  • Hi,

    Is the FYTD defined as a calculated member of the fiscal year hierarchy?
    How is the Year over Year Growth template defined in your cube? Is it's definition before or after the FYTD definition in the calculation script (if FYTD is defined there)?

    Which version of Analysis Services are you using?

    Frank

    Monday, August 3, 2009 5:45 PM
  • I'd like to add one more question to Frank's.

    Does your date dimension have same day/week/month/quarter a year ago column(s)? They aren't required to do the calculation you're after, but they do remove a layer of complexity from the process.
    Garth H MCTS: SQL 2008 BI http://bitinkering.spaces.live.com/
    Monday, August 3, 2009 5:57 PM
  • No, I don't have the year ago columns.
    Tuesday, August 4, 2009 8:40 PM
  • Hi Margni,

     

    If I understand correctly, try to create a calculated measure on [Adventure Works], with expression:

    IIF(

           SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

           )=0

         , "",

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/

          SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          ) - 1

       )

     

    It will compare the total value in current period with lat period. You can also test the results with below code:

     

    WITH MEMBER Measures.[Change Amount Rate] AS

       IIF(

           SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

           )=0

         , "",

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])/

          SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          ) - 1

       ), FORMAT_STRING = '0%' 

    -------------------------   [Current Periods]

        MEMBER Measures.[Current Periods] AS 

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year],[Date].[Fiscal].CurrentMember),[Measures].[Internet Sales Amount])

    -------------------------   [last Periods]

        MEMBER Measures.[last Periods] AS 

         SUM(PeriodsToDate([Date].[Fiscal].[Fiscal Year], 

              ParallelPeriod([Date].[Fiscal].[Fiscal Year],1,[Date].[Fiscal].CurrentMember))

             ,[Measures].[Internet Sales Amount]

          )

       

    SELECT {Measures.[Current Periods],  Measures.[last Periods], Measures.[Change Amount Rate]} ON 0,

    [Date].[Fiscal].[Month].Members ON 1

    FROM [Adventure Works]

     

    Hope this helps.

    Raymond

    • Marked as answer by Raymond-Lee Thursday, August 13, 2009 10:01 AM
    Wednesday, August 5, 2009 6:21 AM