MDX ClosingPeriod Function

In Multidimensional Expression, ClosingPeriod Function will return the Last Sibling member belongs to the specified member or at specified level.

MDX ClosingPeriod Function Syntax

The basic syntax of the MDX ClosingPeriod is:

CLOSINGPERIOD (Level_Expression, Member_Expression)

Member_Expression: Any Multidimensional Expression that returns valid Member.


Level_Expression: Please specify the level you want to navigate.


In this article we will show you, How to write ClosingPeriod function in MDX query with examples. For this, We are going to use the below show data


Following screenshot shows the List of Calender Years inside the Date Dimension

MDX ClosingPeriod Function Example

In this example we are going to find the Last year present in the Calender Year using ClosingPeriod function.


CODE


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


OUTPUT





Analysis:


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

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the last sibling member present in the Calender Year from the Date Dimension, Which is Year 2014.


CLOSINGPERIOD ([Date].[Calendar].[Calendar Year])


MDX ClosingPeriod Function Example 2


In this example we are going to find the Last Month present in the Calender Year 2013 using ClosingPeriod function and calculate the Internet Sales Amount of that.


CODE


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


OUTPUT

Finding Range Using MDX ClosingPeriod Function Example 1

In this example we will show you, How to find the Range using ClosingPeriod function. The following query will return the Internet Sales amount from Calender Year 2010 to 2014


CODE


SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Date].[Calendar].[Calendar Year].[CY 2010]:
CLOSINGPERIOD ([Date].[Calendar].[Calendar Year]) ON ROWS
FROM [Adventure Works]


OUTPUT





Analysis:

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

[Measures].[Internet Sales Amount] ON COLUMNS

Below line of code will return the last sibling member present in the Calender Year from the Date Dimension, Which is 2014.


CLOSINGPERIOD ([Date].[Calendar].[Calendar Year])

It means, We used the range operator between 2010 and 2014 Year so, the output will display the Internet Sales amount of all the Calender Years present in the Date dimension.

[Date].[Calendar].[Calendar Year].[CY 2010] -- 2010

CLOSINGPERIOD ([Date].[Calendar].[Calendar Year]): -- 2014

Finding Range Using MDX ClosingPeriod Function Example 1


In this example we will show you, How to find the Range using ClosingPeriod function. The following query will return the Internet Sales amount from May to December Month in Calender Year 2013


CODE

SELECT
[Measures].[Internet Sales Amount] ON COLUMNS,
[Date].[Calendar].[Month].[May 2013]:
CLOSINGPERIOD (
[Date].[Calendar].[Month]
,[Date].[Calendar].[Calendar Year].[CY 2013]
) ON ROWS
FROM [Adventure Works]


OUTPUT