Monday, August 4, 2014

Percentage difference from previous parallel period

Let’s start by getting the parallel period:

WITH MEMBER [Measures].[Sales Amount PY]
AS
(
    [Measures].[Sales Amount]
    , ParallelPeriod
        ([Date].[Calendar].[Calendar Year] // Level
        , 1 // number of periods back
        , [Date].[Calendar].CurrentMember) // Start member
)
, format_string = "Currency"

SELECT

{
    (
         [Date].[Calendar Year].[CY 2010]:
         [Date].[Calendar Year].[CY 2012]
        ,[Date].[Calendar Semester of Year].Children)
}
on COLUMNS,
{
     [Measures].[Sales Amount PY]
    ,[Measures].[Sales Amount]
}
on ROWS
FROM [Adventure Works]

image

Next getting the perceptual difference

MEMBER [Measures].[Sales Amount Delta PY]
AS
([Measures].[Sales Amount PY] - [Measures].[Sales Amount])
/
[Measures].[Sales Amount PY]
, format_string = "Percent"

select
{
    (
         [Date].[Calendar Year].[CY 2010]:
         [Date].[Calendar Year].[CY 2012]
        ,[Date].[Calendar Semester of Year].Children)
}
on COLUMNS,
{
     [Measures].[Sales Amount PY]
    ,[Measures].[Sales Amount]
    ,[Measures].[Sales Amount Delta PY]
}
on ROWS
FROM [Adventure Works]

image

The division by NULL will throw an error. One way off solving this is using a CASE WHEN construct like we did in http://101mdx.blogspot.nl/2014/08/percentage-of-one-level-up.html.

One other way is using the the DIVIDE functionality. This offers us the possibility to offer an alternate when there is an error.

MEMBER [Measures].[Sales Amount Delta PY Divide]
AS
DIVIDE(
    ([Measures].[Sales Amount PY] - [Measures].[Sales Amount])
    , [Measures].[Sales Amount PY]
    , 0) // Alternate on error or division by zero
, format_string = "Percent"

select
{
    (
         [Date].[Calendar Year].[CY 2010]:
         [Date].[Calendar Year].[CY 2012]
        ,[Date].[Calendar Semester of Year].Children)
}
on COLUMNS,
{
     [Measures].[Sales Amount PY]
    ,[Measures].[Sales Amount]
    ,[Measures].[Sales Amount Delta PY]
    ,[Measures].[Sales Amount Delta PY Divide]
}
on ROWS
FROM [Adventure Works]

image

Have Fun And Till Next Time

No comments:

Post a Comment