ClosingPeriod

The MDX ClosingPeriod function for Essbase returns the last descendant of a layer, or the last child of the Time dimension.

Syntax

ClosingPeriod ( [ layer [,member ]] )

Parameters

layer: Layer specification.
member: Optional member specification. If omitted, the last child of the Time dimension is assumed (for example, Qtr4 in Sample Basic).

Notes

The return value of this function varies depending on the input.

When both layer and member arguments are given as input, Closingperiod returns the last descendant of the input member at the input layer. For example, Closingperiod(Year.generations(3), Qtr3) returns Sep. If the input member and layer are the same layer, the output is the input member. For example, Closingperiod(Year.generations(3), Sep) returns Sep.
When only the layer argument is specified, the input member is assumed to be the current member of the dimension used in the layer argument. Closingperiod returns the last descendant of that dimension, at the input layer. For example, Closingperiod(Year.generations(3)) returns Dec.
When no arguments are specified, the input member is assumed to be the current member of the Time dimension, and ClosingPeriod returns the last child of that member. Do not use this function without arguments if there is no dimension tagged as Time.

Example

The following query

WITH
MEMBER [Measures].[Starting Inventory] AS
'
IIF (
 IsLeaf (Year.CurrentMember),
  [Measures].[Opening Inventory],
  ([Measures].[Opening Inventory],
   OpeningPeriod (
     [Year].Levels(0),
     [Year].CurrentMember
   )
  )
)'

MEMBER [Measures].[Closing Inventory] AS
'
IIF (
   Isleaf(Year.CurrentMember),
    [Measures].[Ending Inventory],  
   ([Measures].[Closing Inventory],
    ClosingPeriod (
    [Year].Levels(0),
    [Year].CurrentMember
    )
)
)'
SELECT
CrossJoin (
 { [100-10] },
 { [Measures].[Starting Inventory], [Measures].[Closing Inventory] }
)
ON COLUMNS,
Hierarchize ( [Year].Members , POST)
ON ROWS
FROM Sample.Basic

returns the grid:

Table 4-46 Output Grid from MDX Example

(axis)	100-10	100-10
(axis)	Starting Inventory	Closing Inventory
Jan	14587	14039
Feb	14039	13566
Mar	13566	13660
Qtr1	14587	13660
Apr	13660	14172
May	14172	15127
Jun	15127	15580
Qtr2	13660	15580
Jul	15580	14819
Aug	14819	14055
Sep	14055	13424
Qtr3	15580	13424
Oct	13424	13323
Nov	13323	13460
Dec	13460	12915
Qtr4	13424	12915
Year	14587	12915