MDX - SQL Server Analysis Services 2012 Cube Development - page 222

(([Date].[Calendar].CurrentMember,
measures.[Reseller Sales Amount]) -
([Date].[Calendar].CurrentMember.PrevMember,
measures.[Reseller Sales Amount]) )/
([Date].[Calendar].CurrentMember.PrevMember,
measures.[Reseller Sales Amount])
) , "N/A"
),
FORMAT_STRING = "Percent",
NON_EMPTY_BEHAVIOR = { [Reseller Sales
Amount] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP =
'Reseller Sales' ;

2. Save the database solution; Analysis Services objects won't have to be re-
processed, but the MDX script will be executed to apply the calculation you
just added.
3. Open Excel and create a PivotTable based on the Adventure Works 2012
database. Include the Reseller Sales Amount and Year-To-Year
Reseller Sales Growth measures on columns and the
[Date].[Calendar] hierarchy on rows. As shown in the following output,
SSAS applied the calculation to determine that reseller sales grew by 121.50
percent in 2007 and shrank by 49.93 percent in 2008, compared to prior
years respectively. For all other years, Excel displayed N/A because year-
over-year calculation of growth was not applicable. You could use the Nex-
tMember function in a similar way to compare the current values with those
of the next calendar year:

CY 2005 N/A

CY 2006 $3,713,626.10 N/A

CY 2007 $8,225,508.77 121.50%