How to create a period over period Measurement in MDX?

Question

I'm fairly new to MDX queries, and I'm having trouble understanding how I might compute a period over period measurement. For example say I have a metric like revenue, and I'm breaking that revenue out over time say on a Month to Month basis. How would I calculate the change from Month to Month of that revenue as a percent? Now let's say I want to calculate that generically over any period, quarter to quarter, year to year, month to month, or even compare it with identical periods from prior years. 2011 Q1 vs 2012 Q1, 2011 Q2 vs 2012 Q2, etc.

Time Dimension

Schema definition:

<Dimension type="TimeDimension" highCardinality="false" name="Time">
    <Hierarchy name="yearQuarterMonth" caption="Year/Quarter/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="fiscalYearQuarterMonth" caption="Fiscal Year/Quarter/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Fiscal Year" column="fiscal_year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Quarter" column="quarter" type="Numeric" uniqueMembers="false" levelType="TimeQuarters" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="yearMonth" caption="Year/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Year" column="year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
    <Hierarchy name="fiscalYearMonth" caption="Fiscal Year/Month" hasAll="true" primaryKey="id">
        <Table name="Time"/>
        <Level name="Fiscal Year" column="fiscal_year" type="Numeric" uniqueMembers="true" levelType="TimeYears" hideMemberIf="Never"/>
        <Level name="Month" column="month" type="Numeric" ordinalColumn="month" uniqueMembers="false" levelType="TimeMonths" hideMemberIf="Never"/>
        <Level name="Day" column="day" type="Numeric" uniqueMembers="false" levelType="TimeDays" hideMemberIf="Never"/>
    </Hierarchy>
</Dimension>

Bill Anton Bill Anton · Accepted Answer · 2012-03-28T13:43:23

For Period over Period calculations, you will want to look into using the PrevMember function... http://msdn.microsoft.com/en-us/library/ms144719.aspx

Below is a snippet that should give you a good start on Period over Period...

WITH
MEMBER [Measures].[WO Actual Amount PP] AS
    (
         [Order Date].[Calendar].CurrentMember.PrevMember
        ,[Measures].[WO Actual Amount]
    )
    ,FORMAT_STRING = "Currency"
MEMBER [Measures].[Prior Period Growth %] AS
    IIF(
         [Measures].[WO Actual Amount PP] = 0
        ,'N/A'
        ,([Measures].[WO Actual Amount]-[Measures].[WO Actual Amount PP])/[Measures].[WO Actual Amount PP]
    )
    ,FORMAT_STRING = "Percent"
SELECT
    NON EMPTY {
        [Measures].[WO Actual Amount],
        [Measures].[WO Actual Amount PP],
        [Measures].[Prior Period Growth %]
    } ON 0,
    NON EMPTY {
        [Order Date].[Calendar Year].[Calendar Year].Members
    } ON 1
FROM
    [<<cube name>>]

For Same Period Prior Year or calculation, you'll want to look into the ParallelPeriod function... http://msdn.microsoft.com/en-us/library/ms145500.aspx

Below is a snippet that should give you a good start on Year over Year...

WITH
MEMBER [Measures].[WO Actual Amount YoY] AS
    (
         ParallelPeriod(
              [Order Date].[Calendar].[Calendar Year]
             ,1
             ,[Order Date].[Calendar].CurrentMember
         )
        ,[Measures].[WO Actual Amount]
    )
    ,FORMAT_STRING = "Currency"
MEMBER [Measures].[Prior Period Growth %] AS
    IIF(
         [Measures].[WO Actual Amount YoY] = 0
        ,'N/A'
        ,([Measures].[WO Actual Amount]-[Measures].[WO Actual Amount YoY])/[Measures].[WO Actual Amount YoY]
    )
    ,FORMAT_STRING = "Percent"
SELECT
    NON EMPTY {
        [Measures].[WO Actual Amount],
        [Measures].[WO Actual Amount YoY],
        [Measures].[Prior Period Growth %]
    } ON 0,
    NON EMPTY {
        [Order Date].[Calendar Month].[Calendar Month].Members
    } ON 1
FROM
    [<<cube name>>]

How to create a period over period Measurement in MDX?

1 Answers