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

We can now compute the monthly year-to-date sales. In the expression below,
the measure to be displayed is the sum of the current time member over the
year level:

SUM(PERIODSTODATE([Order Date].Calendar.Year,
[Order Date].Calendar.CURRENTMEMBER), Measures.[Sales Amount])

Similarly, by replacing Year by Quarter in the above expression, we can obtain
quarter-to-date sales. For example, the following query shows year-to-date
and quarter-to-date sales:

WITH MEMBER Measures.YTDSales AS
SUM(PERIODSTODATE([Order Date].Calendar.Year,
[Order Date].Calendar.CURRENTMEMBER), Measures.[Sales Amount])
MEMBER Measures.QTDSales AS
SUM(PERIODSTODATE([Order Date].Calendar.Quarter,
[Order Date].Calendar.CURRENTMEMBER), Measures.[Sales Amount])
SELECT { Measures.[Sales Amount], Measures.YTDSales, Measures.QTDSales }
ON COLUMNS, [Order Date].Calendar.Month.MEMBERS ON ROWS
FROM Sales

The result of the query is as follows:

Sales Amount YTDSales QTDSales

July 1996 $27,246.10 $27,246.10 $27,246.10

August 1996 $23,104.98 $50,351.07 $50,351.07

September 1996 $20,582.40 $70,933.47 $70,933.47

October 1996 $33,991.56 $104,925.04 $33,991.56

November 1996 $44,365.42 $149,290.46 $78,356.98

December 1996 $42,559.41 $191,849.87 $120,916.40

January 1997 $57,187.26 $57,187.26 $57,187.26

February 1997 $36,275.14 $93,462.39 $93,462.39

··· ··· ··· ···

As can be seen above, the Northwind data warehouse contains sales data
starting in July 1996. Thus, the value of both measures YTDSales and
QTDSales for August 1996 is the sum of the measure Sales Amount of
July 1996 and August 1996. Similarly, the value of measure YTDSales for
December 1996 is the sum of Sales Amount from July 1996 to December 1996.
This is to be contrasted with the value of measure QTDSales for December
1996, which is the sum of Sales Amount from October 1996 to December 1996.
The xTD ( YTD , QTD , MTD ,and WTD ) functions refer to year-, quarter-,
month-, and week-to-date periods. They are only applicable to a time
dimension (which was not the case for the other functions we have seen so
far). The xTD functions are equivalent to the PeriodsToDate function with a
level specified. YTD specifies a year level, QTD specifies a quarter level, and
so on. For example, in the query above, the measure YTDSales can be defined
instead by the following expression: