Page Comparison

On this page:

Moving in Time

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

...

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.

...

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.

...

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.