Working with Time in Microsoft SQL Server 2008 MDX

Retrieve the periods-to-date for any specified period

Use the PeriodsToDate function to return a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by a specified level of a calendar hierarchy. For example, the following query retrieves the periods-to-date over the calendar year for each of the Month members along the ROWS axis to calculate a year-to-date total for reseller sales:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        PeriodsToDate(
            [Date].[Calendar].[Calendar Year],
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Retrieve the periods-to-date for a year

Use the Ytd function to return a set of sibling members from the same level as a given member, starting with the first sibling and ending with the given member, as constrained by the Year level of a calendar hierarchy. For example, the following query retrieves the year-to-date periods for each of the Month members along the ROWS axis to calculate a year-to-date total for reseller sales:

WITH
MEMBER [Measures].[Year to Date Reseller Sales] AS
    Aggregate(
        Ytd([Date].[Calendar].CurrentMember),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Year to Date Reseller Sales])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

For quarter-to-date, month-to-date, and week-to-date calculations, use the Qtd, Mtd, and Wtd functions, respectively, in a similar manner.

Retrieve a number of prior periods

Use the LastPeriods function to retrieve a set of members up to and including a specified member. For example, the following query retrieves the last three months for each of the Month members along the ROWS axis to calculate a rolling three-month average for reseller sales:

WITH
MEMBER [Measures].[Three Month Avg Reseller Sales Amount] AS
    Avg(
        LastPeriods(
            3,
            [Date].[Calendar].CurrentMember
            ),
        ([Measures].[Reseller Sales Amount])
        )
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Three Month Avg Reseller Sales Amount])
        } ON COLUMNS,
    {[Date].[Calendar].[Month].Members} ON ROWS
FROM [Step-by-Step]

Retrieve a parallel member

Use the ParallelPeriod function to identify a member from a prior period in the same relative position as a specified member. For example, the following query retrieves prior period reseller sales for each of the Month members along the ROWS axis:

WITH
MEMBER [Measures].[Prior Period Reseller Sales Amount] AS
    (
        ParallelPeriod(
            [Date].[Calendar].[Calendar Year],
            1,
            [Date].[Calendar].CurrentMember
            ),
        [Measures].[Reseller Sales Amount]
        )
    ,FORMAT="Currency"
SELECT
    {
        ([Measures].[Reseller Sales Amount]),
        ([Measures].[Prior Period Reseller Sales Amount])
        } ON COLUMNS,
     {
        Descendants(
            [Date].[Calendar].[Calendar Year].[CY 2003],
            [Date].[Calendar].[Month],
            SELF
            )
         } ON ROWS
FROM [Step-by-Step]

Retrieve the opening period or closing period

Use the OpeningPeriod or ClosingPeriod functions, respectively. For example, the following query employs the OpeningPeriod function to retrieve the exchange rate for the first day in each period:

WITH
MEMBER [Measures].[First Child Rate] AS
    (
        OpeningPeriod(
            [Date].[Calendar].[Date],
            [Date].[Calendar].CurrentMember
            ),
             [Measures].[End of Day Rate]
            )
       ,FORMAT="Standard"
SELECT
    {
        ([Measures].[First Child Rate]),
        ([Measures].[End of Day Rate])
        } ON COLUMNS,
    {[Date].[Calendar].Members} ON ROWS
FROM [Step-by-Step]
WHERE ([Destination Currency].[Destination Currency].[Euro])