Period Offset

Often when developing reports, I’ll get a little stumped with a requirement.  It’s not that the request is strange, rather it’s usually perfectly reasonable and I just have trouble remembering how to do it.  One of these requests happened recently.  The report needed to show a 12-month history based on the current month.  This meant the report would have to cross years.  I knew I had configured the functionality in the past, but I could not remember how to do it.  Turns out it was pretty simple.

The function needed is PeriodOffset.  This should not be confused with Offset in the function RelativeMember.

RelativeMember Offset is available on all dimensions.  But to use PeriodOffset, it is only available on the Period dimension.

Configuring this functionality can be tricky since it involves a secondary dimension.  Oracle documentation explains it fairly well.

By utilizing Period and Year dimensions together, PeriodOffset allows the report to systematically query the desired period in the appropriate year. 

1. Select the Period member to start with, it can be a function like CurrentPOV or a member prompt, or even a specific member
2. The Offset value is how many period members you want to go forward or backward from the member selected above
3. The Secondary Dimension is Years
4. Periods below Member is optional unless the Period dimension has additional level 0 members other than months (for example, BegBalance).  In our outline, the Period dimension does have other level 0 members, so we need to identify the parent of the level 0 months.

This needs to be repeated for each column in the report for each month forward or backward you want to go.  Since our report is for trailing 12 months, we have the first column with CurrentPOV for Period and then 11 more columns incremented by -1 to represent the previous months.

The resulting report correctly queries current month and 11 prior with the correct year for each period.

1. Current month and current year
2. Prior month and current year, the Offset is -1
3. 2 months prior to current and the prior year, the Offset is -2
4. 3 months prior to current and the prior year, the Offset is -3

Note that for this functionality to work, Period and Year have to be on the same axis in the report, either both in columns or both in rows.

There you have it, a simple solution to a common requirement.

As always, happy EPM’ng!