Time Intelligence Formula (TI Formula) allows time periods, sets of time periods and ranges to be specified using a simple, easy to understand syntax.
The following are typical scenarios with their corresponding formula and results:
TI Formula consists of Operators, Periods and Functions. The formula is intentionally simple yet robust.
The following operators can be used in TI formula expressions.
Standard time periods supported in PPS include:
Time period functions can be applied to any standard time period using the dot operator. The functions available in PPS include:
Periods “to-date” are a NEW type of TI formula added in Office 14. The result of a to-date period is an aggregation of all time periods to date up to the last completed full period. Incomplete time periods are automatically excluded. They are evaluated to the lowest degree of granularity in the data source by default. For example, if most granular time period in the data source were days, then the month to date expression will aggregate all days from the beginning of the month to the last completed full day in the month. (The opposite is true for standard time periods. They automatically include incomplete periods.)
PPS supports the following “to date” time periods:
Period to date functions allow you to control the granularity of which time periods to include. If for example I want to show the year to date including up to the last completed full month, I can add a full period function.
PPS supports the following full period functions:
It is often useful to compare values for the equivalent set of time periods for the current year, but for the previous year. For example, imagine that the current year to date includes Jan – July of 2010. You may want to compare this value to the same set of time periods for the previous year. In this case Jan – July 2009. This is easy to accomplish using TI formula. Simply add and offset value to the to-date period.
In fact, offsets can be specified on any to-date time period. This also works when a full period function is applies as well. Remember to use parenthesis in this case.
Stephen Handy is a Program Manager on the PerformancePoint Services SharePoint BI team.