Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 193

The functions OPENINGPERIOD and CLOSINGPERIOD return, respec-
tively, the first or last sibling among the descendants of a member at a
specified level. For example, the difference between the sales quantity of a
month and that of the opening month of the quarter can be obtained as
follows:

WITH MEMBER Measures.[Quantity Difference] AS
(Measures.[Quantity]) - (Measures.[Quantity],
OPENINGPERIOD([Order Date].Calendar.Month,
[Order Date].Calendar.CURRENTMEMBER.PARENT))

SELECT { Measures.[Quantity], Measures.[Quantity Difference] } ON COLUMNS,

[Order Date].Calendar.[Month] ON ROWS
FROM Sales

In deriving the calculated member Quantity Difference , the opening period
at the month level is taken for the quarter to which the month corresponds.
If CLOSINGPERIOD is used instead, the query will show sales based on the
final month of the specified season, as shown next.

Quantity Quantity Difference

July 1996 1,425

August 1996 1,221 -204

September 1996 882 -543

October 1996 1,602

November 1996 1,649 47

December 1996 1,996 394

··· ··· ···

The PERIODSTODATE function returns a set of periods (members) from
a specified level starting with the first period and ending with a specified
member. For example, the following expression defines a set containing all
the months up to and including June 1997:

PERIODSTODATE([Order Date].Calendar.Year,
[Order Date].Calendar.[June 1997])

Suppose now that we want to define a calculated member that displays
year-to-date information, for example, the monthly year-to-date sales. For
this, in addition to PERIODSTODATE , we need to use the SUM function,
which returns the sum of a numeric expression evaluated over a set. For
example, the sum of sales amount for Italy and Greece can be displayed with
the following expression:

SUM( { Customer.Country.Italy, Customer.Country.Greece } ,
Measures.[Sales Amount])