MDX PeriodsToDate Function

In Multidimensional Expression, PeriodsToDate Function is used to navigate between same level members and returns the set of sibling members from First Sibling to the specified member For example, If you want to list the members from one position to particular member then we can use this PeriodsToDate function.

MDX PeriodsToDate Function Syntax

The basic syntax of the MDX PeriodsToDate is:

1	PERIODSTODATE (Level_Expression, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.

Member_Position: Please specify the position of a member you want to Navigate.

NOTE: LastPeriods Function is used to navigate between same Members level. For example, If we mentioned City member as Member_Expression then LastPeriods function will navigate between remaining Cities, it will not go Level Up (State – Province) or Level down (Postal Code).

In this article we will show you, How to write PeriodsToDate Function examples. For this, we are going to use below shown data.

MDX PeriodsToDate Function Example 1

In this example we will show you, What happen when we use Zero value for the LastPeriods Function. The following query will return the Internet Sales Amount of Months present in Calender Year 2013 until it reaches July 2013

CODE

1 2 3 4 5 6 7

SELECT   [Measures].[Internet Sales Amount] ON COLUMNS,   PERIODSTODATE (             [Date].[Calendar].[Calendar Year],             [Date].[Calendar].[Month].[July 2013]                 ) ON ROWS FROM [Adventure Works]

OUTPUT

Analysis:

In the above MDX Query, We used [Reseller Sales amount] on the columns

1	[Measures].[Reseller Sales Amount] ON COLUMNS

Below line of code will check

1 2 3 4

PERIODSTODATE (              [Date].[Calendar].[Calendar Year],              [Date].[Calendar].[Month].[July 2013]                 )

for the France parent (Which is All member) and then finds the Last child member of the Country.

MDX PeriodsToDate Function Example 2

In this example we will show you, What happen when we use Positive  integer value in LastPeriods Function. The following query will return the Internet Sales amount of the Calender Year 2013, 2012 and 2011 because LastPeriods function with Positive value (3) will move back 2 years + year 2013. This is because LastPeriods function includes 2013 itself.

CODE

1 2 3 4 5 6 7

SELECT   [Measures].[Internet Sales Amount] ON COLUMNS,   PERIODSTODATE (             [Date].[Calendar].[(All)],             [Date].[Calendar].[Calendar Year].[CY 2013]                 ) ON ROWS FROM [Adventure Works]

OUTPUT

Thank You for Visiting Our Blog