YOY calculation

• 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

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