SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Stairway to MDX - Level 13: MDX Time/Date Series Functions: LastPeriods() and ParallelPeriod() Functions

By Bill Pearson,

The Series

This article is part of the Stairway Series: Stairway to MDX

Multidimensional Expressions (MDX) is a standard query language, derived from SQL but geared specifically for OLAP databases. It also includes a calculation language, with syntax similar to spreadsheet formulas. It is an important skill for PowerPivot. Bill's new series for MDX starts right at the very beginning and takes us through all the basic functions of MDX, with plenty of practical examples.

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.

Note: The Date dimension is generically called a “Time” dimension – particularly in pre-2005 versions of SQL Server Analysis Services (“SSAS”), as well as in property settings in even later versions, and in various parts of the general documentation (such as in the MSDN reference cited above). I regularly refer to it as both a “Time” and a “Date” dimension specification, as the Date dimension is commonly implemented in the vast majority of cubes, with a separate Time Dimension also appearing in a smaller percentage - as a best practice for analyzing the time levels (hours, minutes, seconds) of given dates through juxtaposition with the primary Date dimension.

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

  1. 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.

  1. 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:

  1. 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.

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

Illustration 6: Creating a New Query via the Current Connection

  1. 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

  1. 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.

NOTE: for more information on the .CurrentMember function, see Stairway to MDX - Level 10: “Relative” Member Functions: .CurrentMember, .PrevMember, and .NextMember.

We note also that the calculated measure produces another desirable effect, in that it exerts a flattening effect upon the variable monthly Internet Sales values and makes overall trends easier to isolate (although Adventure Works appears to be enjoying consistently increasing revenue over the vast majority of the time range captured in the sample cube).

  1. Save the query by selecting File Save MDXQuery2.mdx As …, naming the file SMDX013-002.mdx, and placing it, as we did with the query we constructed earlier, in a meaningful location.

Now that we've seen how to use the LastPeriods() function in a way to smooth variability in our analysis of trends, let's take a look at another function we have in our MDX toolset to help us to refine the perspective with which we compare historical to current values. In the section that follows, we will gain some practical exposure to the ParallelPeriod() function.

The ParallelPeriod() Function

The ParallelPeriod() function, according to the Microsoft Developer Network, “returns a member from a prior period in the same relative position as a specified member.” ParallelPeriod(), like LastPeriods(), and many other functions I include in the “Time / Date series” group, is primarily intended to be used with the Time / Date dimension, but can actually be used with any dimension.

The ParallelPeriod() function might serve, as an illustration, to make it easy for us to navigate to “our current point in the year, last year,” be the “current point” a month, quarter, or other period supported by our cube structure.

ParallelPeriod() allows us to meet a business need that is common to virtually all industries. It allows us to return, for a given period, a value for its parallel in another time frame. ParallelPeriod() allows us to compare, for example, the sales over a given month with the sales that took place over the same month in the prior year, or a for a quarter this year over the same quarter last year. This might be particularly useful in a business whose revenues are highly seasonal. Consider, as an illustration, the case of a retail organization for whom Q4 – a large portion of which is Christmas shopping season – might be more usefully compared to Q4 of the prior year, rather than to Q3 of this year, when sales might have been (unsurprisingly) less.

As is the case with most of the time series functions, other, less direct approaches exist to meet business requirements of this nature. But ParallelPeriod() provides an easy, time-focused approach to this common need, as we will see in the sections that follow.SQL Server 2012 SQL Server 2008 R2 SQL Server 2008 SQL Server 2005 ParallelPeriod() is intuitively named, as are many of the time / date series functions. Although we might craft (via skillful use of its more flexible arguments) the PeriodsToDate() function, or even accomplish its goals through other, less direct ways, ParallelPeriod() is specifically designed for what is a common business requirement.

Syntactically, three arguments are placed within the parentheses to the right of ParallelPeriod(), as shown here:

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

Similar to the case of the Cousin() function (see Stairway to MDX - Level 9: Member “Family” Functions: .LastSibling and Cousin()), the ParallelPeriod() function operates upon the order and position of members within levels; ParallelPeriod() returns the child member with the same relative position under a given parent member as the specified child member under its own parent. The ParallelPeriod() function is more specifically adapted to time / date dimensions than Cousin(). It takes the ancestor of the specified member (in the typical case, a period), at a specified level, then looks at the specified sibling of the ancestor (who lags by a specified numeric expression) and returns the parallel period of the specified member from the descendants of that sibling.

There are several default considerations in play with the ParallelPeriod() function:

  • If neither a Level Expression nor a Member Expression is supplied to the function, the default Member value is the current member of the first hierarchy on the first dimension (for the associated measure group) with a type of Time
  • Level_Expression.Hierarchy.CurrentMember becomes the default Member value in cases where a Level Expression is specified but a Member Expression is not supplied
  • The default Level is the level of the parent of the specified member
  • The default Index, or “lag,” value is 1.

The following example expression would return each of the “parallel” months (the “same month a year ago,” respectively) for a series of current member months from calendar years 2007 and 2008:

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

Let's extend the above expression into a working example, once again. With this example, we'll again 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-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]

The dataset returned would appear as depicted:

Illustration 9: Example Result Dataset Using the ParallelPeriod() Function

Let's walk through what has transpired: We are employing the ParallelPeriod() function to select and return a value, within the context of a member (calendar month) from a prior period (calendar year level of the Date dimension, with a “lookback” index of “1”) in the same relative position (calendar month with respect to its ancestor year) as a specified member (current member).

Let's get some hands-on exposure to ParallelPeriod(), to reinforce our understanding of the basics we've discussed. We will again use the MDX Query Editor in SSMS to construct and execute the MDX we examine, and to view the result datasets we obtain.

Let's kick off our practice session with an exercise that first employs the ParallelPeriod() function, again in a rudimentary way to demonstrate its basic operation, this time in conjunction with additional, related calculations: Let's say that a particularly demanding Adventure Works Cycles colleague has asked that we provide a dataset that contains three elements: the Year-to-Date Internet Sales value for each month within the current year (Calendar Year 2007), as well as the same calculation – cast within the context of the same month the year previous – in the column alongside the first calculation. To add further value, a third calculation will present the change of the Year-to-Date value from one year to the next.

Once we confirm our understanding of the business requirement at hand, we begin the process of constructing a query to retrieve the specified dataset via the steps that follow:

  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-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]

Our input appears in the Query pane:

Illustration 10:  Practice Query, Employing ParallelPeriod(), in the Query Pane

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

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

Illustration 11: Example Result Dataset Using the ParallelPeriod() Function

We have addressed all considerations contained within the business requirement received. Of immediate interest in this exercise has been our use, within our second calculated measure, of the ParallelPeriod() function to return, alongside the Year-to-Date Internet Sales calculation on the column axis, the Year-to-Date Internet Sales corresponding to the respective month of the previous year. This demonstrates the power of MDX from the perspective of Time Intelligence, in that we can define a calculation for the current period, and then, by simply enclosing that calculation's name within the ParallelPeriod() function, we can return the values for the calculation from the period parallel within the Date dimension hierarchy – in this case, for the corresponding month members of the previous year.

Visual verification of accuracy of results is straightforward with the rows in the returned dataset comprising two full years. We can easily see a couple of things: 1) that the totals of the Internet Sales Amount for the three months January, February and March of 2007, for example, add up to the corresponding Year-to-Date total for March of that year; and 2) that, for any given 2007 month in the returned dataset, the Prior Period Year-to-Date Internet Sales value agrees with the Year-to-Date Internet Sales value for the same month in 2006.

  1. Save the query by selecting File Save MDXQuery1.mdx As …, naming the file SMDX013-003.mdx, and placing it in a meaningful location.
  2. Exit SSMS as desired.

In conclusion, we can easily see the utility of the LastPeriods() and ParallelPeriod () functions in providing us a relative “route to data” (both basic data and results of calculations) that is typically most useful within the context of time – utility that we can fine-tune with parameters that we can specify within arguments provided to the function. Once again, it becomes obvious that a grasp of the concepts behind the functions is key to the mastery of the powerful MDX skillset.

Summary

In this Level, we continued an introduction to the time / date series functions group that we began in Stairway to MDX Level 11: MDX Time/Date Series Functions: PeriodsToDate() and Derivative Shortcut Functions. In furthering our ongoing examination of some of the common considerations of time / date functions, together with the options that MDX offers us as support in meeting related business needs, we introduced the LastPeriods() and ParallelPeriod() functions. LastPeriods() serves, we learned, to return a set of members up to and including a specified member, whereas ParallelPeriod() returns a member from a prior period in the same relative position as a specified member.

In getting familiar with LastPeriods() and ParallelPeriod(), we examined the syntax structure with which we employ each, as well as other details of operation. We gained the know-how needed to take advantage of the LastPeriods() and ParallelPeriod() functions through the typical Stairway to MDX practice exercises, whereby we constructed queries that used each, and then examined the results datasets those queries delivered. Throughout the Level, we continued to lay the foundation for our ongoing examination of time / date series functions and expressions, together with other capabilities of MDX to help us to meet typical business needs.

 

This article is part of the Stairway to MDX Stairway

Sign up to our RSS feed and get notified as soon as we publish a new level in the Stairway! Rss

Total article views: 2330 | Views in the last 30 days: 6
 
Related Articles
SCRIPT

Calendar Table Function

This iTVF will produce a calendar table that can be used for complex date manipulation; quickly and ...

BLOG

MDX Member Functions

There are a large number of Member functions in MDX that do everything from returning ancestry of a ...

ARTICLE

4-4-5 Calendar Functions, Part 1

New author Cliff Corder has a SQL Server function to report on a 4-4-5 Calendar Accounting Year.

BLOG

LastPeriods and PeriodstoDate

There are several ways for calculating “To-Date” calculations the longer way. These normally include...

SCRIPT

Print Text Month Calendar

This function return the text format of a month calendar. It could be useful for sending through ema...

Tags
lastperiods()    
mdx    
parallelperiod()    
stairway series    
 
Contribute