locked
YOY calculation RRS feed

  • Question

  • hi ,

    I need to calculate YOY for some measures.

    Please provide me the MDX query need to be used in the calculations TAB.


    s dhwani

    Wednesday, May 16, 2012 11:39 AM

All replies

  • Hello,

    We can use the ParallelPeriod() function.

    an example against the Adventure Works sample:

    WITH
    MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
    	(
    		ParallelPeriod(
    			[Date].[Calendar].[Calendar Year],
    			1,
    			[Date].[Calendar].CurrentMember
    			),
    		[Measures].[Reseller Sales Amount]
    		)
    	,FORMAT="Currency"
    MEMBER [Measures].[YOY growth] AS
    
        IIF
        (
          [Measures].[Prior Period Reseller Sales Amount] = 0
         ,null
         ,
    
    	(
    		([Measures].[Reseller Sales Amount])-
    			([Measures].[Prior Period Reseller Sales Amount])
    		) /
    		([Measures].[Prior Period Reseller Sales Amount])
    		)
    	,FORMAT="Percent"
    SELECT
    	{
    		([Measures].[Reseller Sales Amount]),
    		([Measures].[Prior Period Reseller Sales Amount]),
    		([Measures].[YOY growth])
    		} ON COLUMNS,
    [Date].[Calendar Year].[Calendar Year] ON ROWS
    FROM [Adventure Works]

    similar code comparing monthly sales:

    WITH 
      MEMBER [Measures].[Prior Period Reseller Sales Amount] AS 
        (
          ParallelPeriod
          (
            [Date].[Fiscal].[Fiscal Year]
           ,1
           ,[Date].[Fiscal].CurrentMember
          )
         ,[Measures].[Reseller Sales Amount]
        ) 
       ,FORMAT_STRING = 'Currency' 
      MEMBER [Measures].[Reseller Sales YoY %] AS 
        IIF
        (
          [Measures].[Prior Period Reseller Sales Amount] = 0
         ,null
         ,
            [Measures].[Reseller Sales Amount]
          / 
            [Measures].[Prior Period Reseller Sales Amount]
        ) 
       ,FORMAT_STRING = 'Percent' 
    SELECT 
      {
        [Measures].[Reseller Sales Amount]
       ,[Prior Period Reseller Sales Amount]
       ,[Reseller Sales YoY %]
      } ON 0
     ,{[Date].[Fiscal].[Month].MEMBERS} ON 1
    FROM [Adventure Works];

    Philip,



    • Edited by VHteghem_Ph Wednesday, May 16, 2012 12:13 PM
    • Proposed as answer by Lola Wang Thursday, May 17, 2012 6:43 AM
    Wednesday, May 16, 2012 12:02 PM