Working with Time Related MDX Functions

This article covers various MDX functions that allow you to work with time related dimensions.

These functions are not strictly for time related dimensions as they work on any dimension. However, using time related dimensions makes it a bit easier to explain and the examples more relevant.

For these examples I have a Period dimension that has two rollups in the Default hierarchy. One that simply aggregates Months into Years and then into Total Years. The other aggregates Months into Quarters then Halves then Full Years and then an overall total.

The }HierarchyProperties have been configured and relate to the second rollup i.e. Total Years by Quarters.

Years rollup in the Period Dimension

Halves and Quarters rollup within the Period dimension

Levels configuration in }HierarchyProperties

Don’t forget to run a RefreshMDXHierarchy if you make changes.

Ideally you would want to create additional named hierarchies if you were going to use multiple named levels or wanted to use some of the MDX below without using the Member Unique Name (MUN) to fully qualify the member. I have specifically created the rollups with Total Years by Quarter and linked the named levels in order to show how to reference the members in the MDX examples.

With the scene set, onto some functions and examples.

OpeningPeriod

Returns the first sibling from the descendants of a specified level.
A specified member can be optionally specified as a starting point. TM1 seems to require the member expression to resolve the expression.

Syntax:
OpeningPeriod( Level [ , Member ])
In the above syntax Level and Member relate to specified Levels, Members or an expression that returns a Level or Member.

Examples:
Example 1:
{OpeningPeriod([Period].[Period].[Leaves], [Period].[Period].[2021-FY])}

Returns:
2021/01
This is the first member at the Leaves level in the 2021-FY rollup.

Example 2:
{OpeningPeriod([Period].[Period].[Quarters], [Period].[Period].[2021-FY])}

Returns:
2021-Q1
This is the first member at the Quarters level in the 2021-FY rollup.

Example 3:
{OpeningPeriod([Period].[Period].[Leaves],[Period].[Period].[2021-H2])}

Returns:
2021/07
This is the first member at the Leaves level starting at the 2021-H2 member

Related functions:
ClosingPeriod, FirstChild

ClosingPeriod

Returns the last sibling from the descendants of a specified level.
A specified member can be optionally specified as a starting point. TM1 seems to require the member expression to resolve the expression.

Syntax:
ClosingPeriod( Level [ , Member ])

Examples:
Example 1:
{ClosingPeriod([Period].[Period].[Leaves],[Period].[Period].[2021-H2])}

Returns:
2021/12
This is the last Leaves level member in the 2021-H2 rollup.

Example 2:
{ClosingPeriod([Period].[Period].[Quarters],[Period].[Period].[2021-H2])}

Returns:
2021-Q4
2021-Q4 was returned being the last member in the Quarters level in the 2021-H2 rollup.

Related functions:
OpeningPeriod, LastChild

PeriodsToDate

Returns a set of sibling members from the same level as the specified member.
Siblings are related to the specified level in the dimension.

Syntax:
PeriodsToDate( Level [ ,Member ])

Examples:
Example 1:
PeriodsToDate([Period].[Period].[Quarters],[Period].[Period].[2021-Q3^2021/08])

Returns:
2021/07; 2021/08
2021/08 belongs to a Quarters level called Quarter 3. The siblings within Quarter 3 are 2021/07; 2021/08; 2021/09. It follow then that PeriodsToDate for 2021/08 within Quarter 3 would return 2021/07 and 2021/08.

Example 2:
PeriodsToDate([Period].[Period].[Halves],[Period].[Period].[2021-Q4^2021/10])

Returns:
2021/07; 2021/08; 2021/09; 2021/10
2021/10 belongs to 2021-Q4 which is a child of 2021-H2, a Halves level member. Leaf members under 2021-H2 are 2012/07;…;2012/12. The four members returned are thus the expected members.

Note:
You need to be specific about the Levels being used in parameter 1 and use the MUN in parameter 2 to specify the element in that level where multiple rollups exist in the same hierarchy.

Related functions:
MTD, YTD but these do not appear to be supported in TM1 currently.

LastPeriods

Returns a set of members based on a specified count and includes the specified member.

Syntax:
LastPeriods(Count [ ,Member ] )
Count can be positive or negative. Positive values will return predecessors or members that lag the current member.

Examples:
Example 1:
LastPeriods(4 ,[Period].[Period].[2021/08])

Returns:
2021/05; 2021/06; 2021/07; 2021/08

Related functions:
Lag

Lead

Returns a member, a specified number of positions after the specified member on the same level. Only one member is returned, not a set.

Syntax:
Member.Lead(Count)

Examples:
Example 1:
{[Period].[Period].[2021/05].LEAD(13)}

Returns:
2022/06
Returns 2022/06 which is 13 periods hence and on the same level as 2021/05

Example 2:
{[Period].[Period].[2021-Q1].LEAD(5)}

Returns:
2022-Q2
Five quarters from 2021-Q1 is 2022-Q2.

Related functions:
Lag, PeriodsToDate

Lag

Returns a preceding member from the same level as the specified member but based on a specified number of positions. Only one member is returned, not a set.

Syntax:
Member.Lag(Count)

Examples:
Example 1:
{[Period].[Period].[2021/09].LAG(5)}

Returns:
2021/04

Example 2:
{[Period].[Period].[2022-Q2^2022/04].LAG(5)}

Returns:
2021/11
Returns 2021/11 which is 5 periods before 2022/04 on the same level. C Levels between the members are ignored

Related functions:
Lead, LastPeriods

FirstChild

Returns the first child of the specified member.

Syntax:
Member.FirstChild

Examples:
Example 1:
{[Period].[Period].[2021-Q2].FirstChild}

Returns:
2021/04

Example 2:
{[Period].[Period].[2021-FY].FirstChild}

Returns:
2021-H1

Related functions:
LastChild, FirstSibling

LastChild

Returns the last child of the specified member.

Syntax:
Member.LastChild

Examples:
Example 1:
{[Period].[Period].[2021].LastChild}

Returns:
2021/12

Example 2:
{[Period].[Period].[2021-FY].LastChild}

Returns:
2021-H2

Related functions:
FirstChild, LastSibling

FirstSibling

Returns the first sibling of the specified member from the same level.

Syntax:
Member.FirstSibling

Examples:
Example 1:
{[Period].[Period].[2021/05].FirstSibling}

Returns:
2021/01
First leaf level member in the 2021 rollup.

Example 2:
{[Period].[Period].[2021-H2^2021-Q4].FirstSibling}

Returns:
2021-Q3
First quarter in the 2021-H2 rollup.

Related functions:
LastSibling, FirstChild

LastSibling

Returns the last sibling of the specified member from the same level.

Syntax:
Member.LastSibling

Examples:
Example 1:
{[Period].[Period].[2021/05].LastSibling}

Returns:
2021/12
Last leaf level member in the 2021 rollup.

Example 2:
{[Period].[Period].[2021-H1^2021-Q1].LastSibling}

Returns:
2021-Q2
Last quarter in the 2021-H1 rollup.

Related functions:
FirstSibling, LastChild

NextMember

Returns the next member at the same level for the specified member.

Syntax:

Member.NextMember

Examples:
Example 1:
{[Period].[Period].[2021/04].NextMember}

Returns:
2021/05

Example 2:
{[Period].[Period].[2021-Q4].NextMember}

Returns:
2022-Q1
Looking at only quarters in the Period dimension, 2022-Q1 is the next member.

Related functions:
PrevMember, Lead

PrevMember

Returns the previous member at the same level for the specified member.

Syntax:
Member.PrevMember

Examples:
Example 1:
{[Period].[Period].[2021/04].PrevMember}

Returns:
2021/03

Example 2:
{[Period].[Period].[2022-Q1].PrevMember}

Returns:
2021-Q4
Looking at only quarters in the Period dimension, 2021-Q4 is the previous member.

Related functions:
NextMember, Lag

Cousin

Returns the child member with the same position relative to the parent/ancestor member for the specified child member.

Syntax:
Cousin( Member , Ancestor_Member )

Examples:
Example 1:
{Cousin([Period].[Period].[2021/06], [Period].[Period].[2022])}

Returns:
2022/06
2021/06 is the sixth child of 2021 therefore the sixth child of 2022 is returned i.e. 2022/06

Related functions:
Lead, Lag

As noted in the introduction, these functions are not limited to Time dimensions and could be used on any dimension but the results may not be applicable.
Returning the first child of Region B in relation to the first child in Region A may return a member but may be arbitrary. In other cases where you potentially have ordered items, the returned member may well be useful.

The above is not an exhaustive list of functions that could be used with Time dimensions but has hopefully exposed you to those that may be useful in your MDX writing.

As always, please let me know should you find any errors or have questions related to the content.

By George Tonkin, Business Partner at MCi.

Further Reading

One Response