Querying Your Cube - Getting Started with SQL Server 2012 Cube Development - page 132

The Lag function can be used in other calculations, such as returning the sales
amount for the previous year; the way to do this is to use the results of the Lag func-
tion in a tuple.
--Query 5.21
WITH MEMBER [Measures].[Sales Current Year] AS
([Order Date].[Hierarchy].CURRENTMEMBER,
[Measures].[Sales Amount])
MEMBER [Measures].[Sales Previous Year] AS
([Order
Date].[Hierarchy].CURRENTMEMBER.LAG(1),
[Measures].[Sales Amount])
SELECT {[Measures].[Sales Current Year],
[Measures].[Sales Previous Year]} ON 0,
[Order Date].[Hierarchy].[Calendar Year] ON 1
FROM [Adventure Works DW2012];
The statistical functions are another category of functions that can be very powerful
when used. Consider the following query that returns the sales for 2006 and 2007
together with the All level.
--Query 5.22
SELECT
[Measures].[Sales Amount] ON 0,
{[Order Date].[Hierarchy].[All],
[Order Date].[Hierarchy].[Calendar
Year].&[2006],
[Order Date].[Hierarchy].[Calendar
Year].&[2007]
} ON 1
FROM [Adventure Works DW2012];
In this case the All level may not be what you actually want to return. Maybe you
actually would like to return the sum of the sales for 2006 and 2007. To do this you
can use the VISUALTOTALS function that will calculate the sum of sales that are
shown in the result set. A query using VISUALTOTALS would look like this.