In this Level we will continue the introduction to the MDX time / date series functions group we began in Level 11: MDX Time/Date Series Functions: PeriodsToDate() and Derivative Shortcut Functions. As we discussed there, and in the following Level 12: MDX Time/Date Series Functions: OpeningPeriod() and ClosingPeriod() Functions, the vast majority of analysis and reporting relates to the behavior of data within the context of time.

We noted in these two Levels that, while we can certainly meet many time - / date – based business requirements (such as determining values for a prior, current, last, etc., reporting period) by using the .CurrentMember, .PrevMember and .NextMember and other functions, this works mainly because the time / date dimension provides a natural (and intuitive) platform for the demonstration of many functions such as these. Because the need to analyze and report upon data within the context of time / date is pervasive, MDX provides a specialized group of time / date series functions to meet these needs. In Level 11, we gained exposure to the syntax and operation of the PeriodsToDate() function, and then discussed the specialized, predefined “shortcut” functions that are based upon it, including YTD(), QTD(), MTD(), and WTD(). In Level 12, we continued our hands-on exposure to the MDX time / date series functions group with the introduction of the OpeningPeriod() and ClosingPeriod() functions. Finally, in this Level, we will extend our discussion and practice to the MDX time / date series functions group with the introduction of the LastPeriods() and ParallelPeriod() functions.

In this Level, we will introduce two remaining time / date series functions, LastPeriods() and ParallelPeriod(). Like the other members of this specialized functions group, these functions support the common business requirement to produce results in reference to time; another characteristic that they have in common with the rest of the time / date series functions is that, while they are typically used with Time-type dimensions, they can be applied to non-time dimensions, too.

Examples abound, when it comes to analyzing data within the context of time, including the analysis of growth (for instance in revenues or expenses) over periods in time (say, between the years 2007 and 2008). Two other common business requirements that arise are the need to calculate “rolling” averages and to need to perform period-over-period analysis. To generate a rolling average, the primary ingredient is a determination of the set of time / date periods over which the average of the measure concerned is to be computed. This set of periods can be generated, in most cases, with the help of the MDX LastPeriods() function. The ParallelPeriod() function, by contrast, provides excellent support in the retrieval of “parallel time” / date periods. Once we have become familiar with each of these functions, we'll take a look at how they can help us to accomplish these requirements.

In this Level we'll become acquainted with the LastPeriods() and ParallelPeriod() functions, continuing to address, as we have in the previous two Levels of this subseries, some of the common considerations of time / date functions as a part of our exploration. In getting familiar with LastPeriods() and ParallelPeriod(), we'll examine the syntax structure with which we employ each, together with illustrative examples of some of the uses of each in practice exercises. At the conclusion of each exercise we perform within the practice session, we'll discuss the MDX results we obtain.

The LastPeriods() Function

The LastPeriods() function, according to the Microsoft Developer Network, “returns a set of members up to and including a specified member.” LastPeriods(), like other functions I include in referring to the “Time / Date series” group, is primarily intended to be used with the time / date dimension, but can actually be used with any dimension, as I've noted to be the case with many “Time / Date series” group function throughout this subseries.

The LastPeriods() function allows us to meet numerous common business needs, including (and especially) those that require that we return (individually or for accumulation) values from, for example, “several periods back,” up to, and including, the specified period. That specified member might be, for example, a certain quarter for which we would like monthly values over the range of, say, two quarters back through the current quarter. A calculation can be driven for several months' activities, as another example, whose range is determined by the beginning and ending points that result from the index we provide the function.

As we've discussed in previous Levels, balances are maintained up to the current point in time / held at various points in time for many accounts whose activity we might wish to analyze, such as Sales, Inventory, Purchases, and various other accounts that contain values besides dollar amounts, such as shares outstanding, headcount, various quantities, and so forth, in our systems. Values of interest in typical uses of the LastPeriods() function often relate to deltas over the given periods. LastPeriods() is excellent for the derivation of “total activity for the last (whatever number) periods back,” and, thus for a “to date” cumulative total of sorts. Utilitarian beauty appears, again, in the use of the function with .CurrentMember and other “relative” functions, to gain a context sensitive calculation that can flex automatically as time marches on. Running averages and other derivatives of the accumulated totals are obvious, easy byproducts, as well, given formulation within an expression that includes LastPeriods().

Common examples of scenarios where LastPeriods() might come in handy include monthly totals / balances, over a few quarters, for the inventories of a group of products we manufacture and sell, for perhaps comparison to the same amounts for another group of our products, as an aid in determining11. Save the query by selecting whether to discontinue production of a given item or items to allow us to compose a more rapidly moving product mix. Another illustration might be to satisfy the need to analyze activity, particularly over several summer months, in total monthly quantity on hand, for a part that we stock in a Repair Parts account for use in maintaining our air conditioning plant, to ascertain, perhaps, increased demands for parts by an aging system.

MDX provides an excellent means for accumulating these time-ranged activity values with the LastPeriods() function. LastPeriods() affords us a ready means to navigate to the first member of the range for which one endpoint (the specified member) is defined and for which the opposite end of the range is specified by the index, as we shall see. Almost as simple as it sounds, the LastPeriods() function returns (in its typical use within the context of a time / date dimension) the set of periods beginning with the period that follows (or lags) the specified member by a specified index value, less one, up to and including the specified member. Indeed, LastPeriods() resembles PeriodsToDate() (explored in Level 11: MDX Time/Date Series Functions: PeriodsToDate() and Derivative Shortcut Functions), which returns all of the members back to the beginning of a specified period, although the LastPeriods() function returns the number of members that is specified by an index value.

If no Member Expression is supplied to LastPeriods(), the function looks to the single dimension in the model with a Type of Time, and assumes the default level of that dimension for purposes of operation.

Illustration 1: Dimension Type Property Set to “Time” for the Date Dimension, as Found in the Adventure Works Sample Cube (Partially Expanded)

If no dimension is marked as a Time dimension, the function will parse and execute without an error, but will cause a cell error in the client application.

Putting LastPeriods() to work is straightforward, once we grasp its general syntax and operation. Syntactically, two arguments are placed within the parentheses to the right of LastPeriods, as shown here:

  LastPeriods(Index [ ,Member_Expression ] )

The Index (minus one) that is specified tells the function how far to “look back” for purposes of the returned dataset. If the Index is positive, the function returns the set of members whose range is started by the member “lagging” one less than the Index value from the specified Member Expression, and that ends with the Member Expression itself. If the Index is negative, then LastPeriods() returns the set of members that begins with the specified member itself, and ends with the member leading (or “ahead in time”) by the negative Index value, minus one, from the specified member. The number of members returned by LastPeriods() is equal to the absolute value of the Index. Finally, an empty set is returned if the Index value is zero.

So to restate its operation, the LastPeriods() function retrieves a set of consecutive members that include a member specified by the Member Expression supplied, and which are located via the Member Expression and the Index number provided to the function. The following example expression would return the fourteen months preceding, and including, September of calendar year 2007:

LASTPERIODS(
   14, 
      [Date].[Calendar].[Month].[September 2007]
   )

Let's extend the above expression into a working example. With this example, focus on understanding the syntax, and the dataset that it returns, simply by performing a review. We'll get hands-on practice with the function under consideration in the examples that follow this one.

/*  SMDX013-000-Example:  Employ LastPeriods() function to return the Reseller 
    Sales Amount over the set of fourteen (Index) consecutive months preceding, 
    and including, September of calendar year 2007.  */SELECT
   {[Measures].[Reseller Sales Amount]} ON 0,
   {LASTPERIODS(14, [Date].[Calendar].[Month].[September 2007])} ON 1
FROM
   [Adventure Works]

The dataset returned would appear as depicted:

Illustration 2: Example Result Dataset Using the LastPeriods() Function

To summarize the action above briefly: The LastPeriods() function is used in this case to return, on the column axis, the fourteen (Index) consecutive months preceding, and including, the specified Member Expression September of calendar year 2007. The set of members returned begins with August 2006, the month “lagging” 13 (Index value less one) from the Member Expression, and ending with September 2007, the Member Expression itself.

Let's begin our hands-on exposure to LastPeriods(), to reinforce our understanding of the basics we've discussed. We will again work with the MDX Query Editor in SSMS, as is typical within this series, as our tool for constructing and executing the MDX we examine, and for viewing the result datasets we obtain. If you are not sure how to set up SSMS in Analysis Services, see Stairway to MDX - Level 1: Getting Started with MDX, where we detail the steps in a manner that will be largely identical for SSAS 2008R2 and above.

We'll kick off our practice session with an exercise that employs LastPeriods() in a rudimentary way, to demonstrate its basic operation: Let's say that a business analyst with our hypothetical client, Adventure Works Cycles, has asked that we help him to assemble an MDX query to return the Internet Sales Amount for the six months leading up to, and including, April, 2007. We will accommodate him with our first practice exercise.

1. Click / select the Adventure Works cube in the SQL Server Management Studio (“SSMS”) Object Explorer.
2. Click the New Query button above the Object Explorer.
3. Type (or copy and paste) the following query onto the new canvas of the Query pane that opens:

/*  SMDX013-001:  Employ LastPeriods() function to return the Internet 
    Sales Amount over the set of six (Index) consecutive months preceding, 
and including, April of calendar year 2007.  */SELECT
   {[Measures].[Internet Sales Amount]} ON 0,
   {LASTPERIODS(6, [Date].[Calendar].[Month].[April 2007])} ON 1
FROM
   [Adventure Works]

Illustration 3: Initial Practice Query, Employing LastPeriods(), in the Query Pane

4. Click the Execute (!) button in the toolbar.

The Results pane is populated by Analysis Services, and the dataset appears, as partially shown.

Illustration 4: Example Result Dataset Using the LastPeriods() Function

In the foregoing example, we have employed the LastPeriods() function to return, on the column axis, the six (Index) consecutive months preceding, and including, the specified Member Expression, April of calendar year 2007. The set of members returned begins with November 2006, the month “lagging” 5 (Index value less one) from the Member Expression, and ending with April 2007, the specified Member Expression.

5. Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX013-001.mdx, and placing it in a meaningful location.

As we learned in the syntax discussion earlier, the rows axis of the result dataset is equivalent to:

{ [Date].[Calendar].[Month].[November 2006],
[Date].[Calendar].[Month].[December 2006],
[Date].[Calendar].[Month].[January 2007],
[Date].[Calendar].[Month].[February 2007],
[Date].[Calendar].[Month].[March 2007],
[Date].[Calendar].[Month].[April 2007] }

We can easily prove that the two are equivalent by taking the following steps:

6. Type the following query into the Query pane:

-- SMDX013-001 - Proof:  Proof of Query SMDX013-001
SELECT    
   {[Measures].[Internet Sales Amount]} ON 0,    
   { [Date].[Calendar].[Month].[November 2006],
      [Date].[Calendar].[Month].[December 2006],
         [Date].[Calendar].[Month].[January 2007],
            [Date].[Calendar].[Month].[February 2007],
         [Date].[Calendar].[Month].[March 2007],
      [Date].[Calendar].[Month].[April 2007] }
    ON 1
FROM
   [Adventure Works]

We retrieve an identical data set, proving the concept behind the operation of LastPeriods() from another perspective:

Illustration 5: Verification of the Previous Results Dataset

At this point, let's get some more hands-on exposure with LastPeriods(), this time in a more extended application of what we have learned. Let's say that the same Adventure Works client colleague approaches us with a whole new requirement. Management has shown interest in performing trend analysis of enterprise Internet Sales. More specifically, they want to try working with a simple “rolling average,” starting with a 6-month cycle, which they may modify once they have time to observe the initial results. They feel that such an average will be but a beginning point in other trend analysis approaches. Moreover, they expect that rolling averages of this sort will have a “flattening” effect that might make it easier to gain more meaningful insight than simply looking at the wider variations that actually occur between months for the base Internet Sales Amount measure.

We will also retrieve the underlying base measure into the returned dataset, even though it was not specifically requested. To do so, as we shall see, provides a built-in visual verification of accuracy and completeness of the calculated measure we deliver to meet the business requirement.

7. From our current position within the Query Editor, select File New from the top menu.
8. Select Query with Current Connection from the cascading menu that appears next, as shown.

Illustration 6: Creating a New Query via the Current Connection

9. Type (or copy and paste) the following query into the Query pane:

/*  SMDX013-002:  Employ LastPeriods() function to calculate a 
    six-month "rolling average" for Internet Sales Amount  */WITH
MEMBER
   [Measures].[6-Mo Internet Sales Average]
AS
   AVG(
       LASTPERIODS(6, [Date].[Calendar].CURRENTMEMBER),
  ([Measures].[Internet Sales Amount])
   )
SELECT
   {[Measures].[Internet Sales Amount], 
       [Measures].[6-Mo Internet Sales Average]} ON 0,
   NON EMPTY {[Date].[Calendar].[Month].MEMBERS} ON 1
FROM
   [Adventure Works]

The query appears within the Query pane:

Illustration 7: Query Employing LastPeriods() to Generate a “Rolling” Average

10. Click the Execute (!) button in the toolbar, once again.

Analysis Services populates the Results pane, as partially shown:

Illustration 8: The Query Results, “Rolling” Six-Month Internet Sales Average, Delivered via the Combination of LastPeriods() and .CurrentMember (Partial View)

In this case, instead of using the LastPeriods() function to simply specify a set along the ROWS axis, as we did in our first practice exercise, we are using LastPeriods() to define the month set, within our 6-Mo Internet Sales Average calculated measure, over which we wish to average the Internet Sales Amount. We can easily verify that the calculation is generating accurate values, with LastPeriods() retrieving the six-month range preceding and including the current member month on any given row. The use of .CurrentMember, of course, makes the output of LastPeriods() dynamic, providing the “rolling” effect that forms a component of the business requirement.

ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )

PARALLELPERIOD(
   [Date].[Calendar].[Calendar Year],
      1,
         [Date].[Calendar].CURRENTMEMBER
   )

/*  SMDX013-001-Example:  Employ the ParallelPeriod() function 
    to identify and return a member (calendar month) from a prior period 
    (calendar year, with an "lookback" index of "1") in the same relative 
position (calendar month with respect to its ancestor year) as a 
specified member (current member).*/WITH
MEMBER 
   [Measures].[PY Internet Sales Amount] 
AS
   (
      PARALLELPERIOD
     (
            [Date].[Calendar].[Calendar Year],
               1,
            [Date].[Calendar].CURRENTMEMBER
         ),
      [Measures].[Internet Sales Amount]
    ),
       FORMAT="$#,#.00"
 
SELECT
     {
        ([Measures].[Internet Sales Amount]),
    ([Measures].[PY Internet Sales Amount])
     } ON 0,
     {
        [Date].[Calendar].[Month].[January 2007]:
[Date].[Calendar].[Month].[December 2008]
        } ON 1
FROM
   [Adventure Works]

/*  SMDX013-003:  Create a query to retrieve Internet Sales Amount for each
of the months within operating calendar years 2006 and 2007.  Add a calculated 
measure to generate Year-to-Date Internet Sales, alongside the same calculated 
measure cast within the "parallel period" of the previous year. Moreover, add a 
"delta" calculation to provide further value via the returned dataset. */WITH
MEMBER
[Measures].[Year-to-Date Internet Sales]
AS
AGGREGATE(
PERIODSTODATE(
[Date].[Calendar].[Calendar Year],
[Date].[Calendar].CURRENTMEMBER
),
([Measures].[Internet Sales Amount])
),
FORMAT="$#,#.00"
MEMBER
[Measures].[Prior Period Year-to-Date Internet Sales]
AS
(
PARALLELPERIOD(
[Date].[Calendar].[Calendar Year],
   1,
        [Date].[Calendar].CURRENTMEMBER
   ),
[Measures].[Year-to-Date Internet Sales]
),
FORMAT="$#,#.00"

MEMBER
[Measures].[Change Annual Year-to-Date Internet Sales]
AS 
([Measures].[Year-to-Date Internet Sales] 
- [Measures].[Prior Period Year-to-Date Internet Sales]
),
FORMAT="$#,#.00"
SELECT
{
([Measures].[Internet Sales Amount]),
([Measures].[Year-to-Date Internet Sales]),
([Measures].[Prior Period Year-to-Date Internet Sales]),
([Measures].[Change Annual Year-to-Date Internet Sales])
} ON 0,
    {
[Date].[Calendar].[Month].[January 2006]:
    [Date].[Calendar].[Month].[December 2007]
} ON 1
FROM
[Adventure Works]