Calculated members in Time dimension

Moving in Time

Time is an important dimension in flex.bi, this session explains how to use measures with Time dimension

As you can move to a different dimension hierarchy levels you can also move between different dimension members in the same dimension. It is typically used in the Time dimension when you want to compare measures between different time periods or aggregate a time period range.

For example, a calculated measure [Measures].[Sales monthly growth] with a formula:

[Measures].[Store Sales] - ([Measures].[Store Sales], [Time].CurrentMember.PrevMember)

will calculate a [Measures].[Store Sales] growth compared to the previous Time dimension member period. If this calculated measure will be combined in a report with a month in the Time dimension then it will show a growth comparing to the previous month. If it will be combined with a year then it will show the growth comparing to the previous year.

There are several functions that will help you “to move in time”:

• [Time].CurrentMember.PrevMember returns the previous member, which contains data, in the same hierarchy level (it will return an empty member for the first member)

• [Time].CurrentMember.NextMember will return the next member

• [Time].CurrentMember.Lag(2) will return the previous member with a distance 2 (use any number for the argument, Lag(1) is the same as PrevMember)

• [Time].CurrentMember.Lead(2) will return the next member with a distance 2 (Lead(1) is the same as NextMember)

• ParallelPeriod([Time].[Year], 1, [Time].CurrentMember) will return the “parallel” Time member one year ago (e.g. for the Day level member Jan 01 2012 it will be the day Jan 01 2011 but for the Month level member Jan 2012 it will be the month Jan 2011)

• OpeningPeriod([Time].[Day], [Time].CurrentMember) will return the first descendant of the current Time member at the Day level (the first day of a year, a quarter or a month which is defined in the Time dimension)

• ClosingPeriod([Time].[Day], [Time].CurrentMember) will return the last descendant of the current Time member at the Day level (the last day of a year, a quarter or a month which is defined in the Time dimension)

If a cube has only one Time dimension then the [Time].CurrentMember argument can be omitted as it will be used by default. So you can use shorter expressions ParallelPeriod([Time].[Year], 1), OpeningPeriod([Time].[Day]), ClosingPeriod([Time].[Day]).

Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use .Name member property to get a member name as a calculation formula result. For example, define the calculated measure [Measures].[test opening day] with a formula:

OpeningPeriod([Time].[Day], [Time].CurrentMember).Name

and use it in report together with the Time dimension members and see if you get the expected result for the Time dimension members at different levels.

Set of time periods

There are additional useful set selection methods for Time periods returns all periods from the beginning of the current member in the specified level (in this example from the beginning of the current year) until the specified member.

• YTD([Time].CurrentMember) is a shorter version of the same function (an abbreviation from Year-to-date).

  Note that even though Weekly hierarchy also has Year level, this function will only work in default Time hierarchy. For weekly hierarchy use the PeriodsToDate([Time.Weekly].[Year]).

• QTD([Time].CurrentMember) returns a quarter-to-date set of members

• MTD([Time].CurrentMember) returns a month-to-date set of members

• WTD([Time.Weekly].CurrentMember) returns a week-to-date set of members

As mentioned earlier If you have just one Time dimension in your cube then you can use even shorter expressions YTD(), QTD(), MTD() and WTD() as by default, they will receive as an argument the current member of the time dimension – [Time].CurrentMember. But sometimes you need to pass a different argument to these functions. For example, if you would like to compare current year-to-date aggregates with year-to-date aggregates a year ago then you can use the following expression to get a year-to-date set for the corresponding Time dimension member a year ago:

YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember))

And if you like a shorter expression then you can omit the current member argument here as well – YTD(ParallelPeriod([Time].[Year], 1)). Most of the time-related functions will use [Time].CurrentMember as a default member argument.

If you want to get a number of last periods from the Time dimension then you can use the function LastPeriods. For example:

LastPeriods(3, [Time].CurrentMember)

will return a set with the current Time member and the two previous members. If you would like to get the current and the next two Time periods then use negative value -3 instead of 3. And you can omit the last argument for this function as well and use LastPeriods(3).

When you want to get the current Time dimension member in the Weekly hierarchy then you need to use member expression[Time.Weekly].CurrentMember. Sometimes you need to write a formula which should use either [Time].CurrentMember (when creating e.g. monthly report) or [Time.Weekly].CurrentMember (when creating a weekly report). In this case, you can use:

[Time].CurrentHierarchyMember

which will return either the Time main hierarchy or the Weekly hierarchy current member depending on which Time hierarchy you use in your report.