MDX-7 Calculated Members in MDX and Relative member functions

Calculated Members:

Calculated members, in short, allow us to define new members, based upon dimensions or measures that exist within the cube in which we create the calculated member. They are members whose values depend on an expression rather than the value of a cell within a cube. The potential uses of calculated members are limited only by the imagination and experience of the developer

To create a calculated measure, we will use the WITH clause, the syntax for which might resemble the following:

WITH MEMBER dimension.name AS 'Expression'

Dimension represents the dimension into which we are creating the new member. 

Name is my shorthand for the name of the member we are creating, and

expression is the expression from which it is created.

WITH 
    MEMBER [Measures].[Warehouse Margin] AS
    '[Measures].[Warehouse Sales]-[Measures].[Warehouse Cost]'
 
 SELECT
 
    {([Time].[Quarter].Members)} ON COLUMNS,
    {([Warehouse].[Country].USA)} ON ROWS
 
 FROM Warehouse
 
 WHERE ([Measures].[Warehouse Margin])

As we can see above, the WITH section contains the definition of the calculated member Warehouse Margin. The newly created Warehouse Margin is a member of the Measures dimension, and is composed of the total of Warehouse Sales less Warehouse Cost, intersecting all members of the remaining dimensions in the cube, but displaying the combined intersects with theUSA warehouses for purposes of our query. 

The returned dataset, therefore, represents theWarehouse Profit (which we called Warehouse Margin in our query) for the USA warehouses, by quarter (for the years 1997 and 1998, as stored in the Warehouse sample cube).

.CurrentMember, .PrevMember and .NextMember are the relative members:

.CurrentMember is actually the default operator, and its specification is, therefore, optional.

At any given point in the execution / evaluation process, the coordinates at which the process "currently" rests is made up of eachdimension in the cube and a current member (dimension1.CurrentMember, dimension2.CurrentMember, dimension3.CurrentMember, and so on).

Syntax
Let's look at an example to familiarize ourselves with the syntax.
 WITH 
    MEMBER [Measures].[Warehouse Margin] AS
    '([Time].CurrentMember , [Measures].[Warehouse Sales])-   
        ([Time].Currentmember, [Measures].[Warehouse Cost])'
 
 SELECT
 
    {(Time.CurrentMember)} ON COLUMNS,
 
    {([Warehouse].[Country].USA)} ON ROWS
 
 FROM Warehouse
 
 WHERE ([Measures].[Warehouse Margin])
The result dataset returned would appear as shown in Illustration 2 below: