For the final post in this series, we’ll take a look at the PERIODSTODATE() function. Your skills of predictive analytics should suggest that this is a handy one to use for reports needing calculations for year-to-date, month-to-date, etc.
The PERIODSTODATE() function takes two inputs:
- [Level]: The hierarchy level defining the “Period” part of the period-to-date
- [Ending Member]: The member that will form the end of the resulting set as well as the level at which the members will reside
It returns the set of members at the same level as the [Ending Member] going back to the beginning of the [Level] hierarchy level. That’s a fun one to try to explain in one sentence. Let’s look at an example and it will become clearer.
Let’s suppose we find ourselves on January 10th, 2011, as shown in Image 1.
Image 1

The MDX expression for this member of my Date Hierarchy would be:
[Date].[Date Hierarchy].[Full Date].[2011-01-10]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
If we wanted to find all of the [Full Date] members for the month of January in 2011 up to and including the [2011-01-10] member, we could use the PERIODSTODATE() function like this:
PeriodsToDate
(
[Date].[Date Hierarchy].[Calendar Month] -- Level Boundary
, [Date].[Date Hierarchy].[Full Date].[2011-01-10] -- Ending Member
)
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
The expression above specifies that we want to return the set of members that would defined the month-to-date starting with January 10th, 2011.
Our resulting set is shown highlighted in Image 2.
Image 2

An MDX expression for the resulting set would be:
{
[Date].[Date Hierarchy].[Full Date].[2011-01-01]
, [Date].[Date Hierarchy].[Full Date].[2011-01-02]
, [Date].[Date Hierarchy].[Full Date].[2011-01-03]
, [Date].[Date Hierarchy].[Full Date].[2011-01-04]
, [Date].[Date Hierarchy].[Full Date].[2011-01-05]
, [Date].[Date Hierarchy].[Full Date].[2011-01-06]
, [Date].[Date Hierarchy].[Full Date].[2011-01-07]
, [Date].[Date Hierarchy].[Full Date].[2011-01-08]
, [Date].[Date Hierarchy].[Full Date].[2011-01-09]
, [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
You will note that the resulting set includes all of the members back to the beginning of January in 2011. Since are Ending Member was at the [Full Date] level of the hierarchy, the resulting members are at that level as well. And, since the Level specified was [Calendar Month], we therefore get all [Full Date] members from our Ending Member back to the very first [Full Date] within our Ending Member’s [Calendar Month].
Note: Since you have been following this series with the utmost diligence for the last ten weeks, you will recall the explanation of the Range operator in the Intro To MDX Decathlon – Sets: A Shirt of a Different Color (4/10) post. Therefore, you will know that a more succinct expression for the above set would be:
{
[Date].[Date Hierarchy].[Full Date].[2011-01-01]
: [Date].[Date Hierarchy].[Full Date].[2011-01-10]
}
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Let’s look at some examples from my Borg cube.
Query 1 uses the PERIODSTODATE() function to return all of the [Calendar Month] members of the 1990 [Calendar Year] up to and including the [1990-04] member, ie April.
Query 1
SELECT
[Measures].[Episode Count] ON COLUMNS
, PeriodsToDate
(
[Date].[Date Hierarchy].[Calendar Year] -- Period to encompass
, [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 1

Our result is January thru April of 1990.
Query 2 uses the same ending member but this time specifies [Calendar Quarter] as the level.
Query 2
SELECT
[Measures].[Episode Count] ON COLUMNS
, PeriodsToDate
(
[Date].[Date Hierarchy].[Calendar Quarter] -- Period to encompass
, [Date].[Date Hierarchy].[Calendar Month].[1990-04] -- Where to start
) ON ROWS
FROM [Borg]
WHERE [Series].[Series Name].[Star Trek: The Next Generation]
.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, “Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
Result 2

Since the [Calendar Month] member specified, [1990-04], is the first [Calendar Month] member within its [Calendar Quarter], our result is just the [1990-04] member we originally specified.
This brings my first blog series to a close. I hope I was able to show that MDX does not have to be intimidating. I encourage you to try the MDX examples you saw here or use them as a model for trying MDX in your own environment.