Time Intelligence Formula Quick Reference
Time Intelligence Formula (TI Formula) allows time periods, sets of time periods and ranges to be specified using a simple, easy to understand syntax.
Formula Structure
| [ ( ] <Period> [ <Offset> [ ) ][ .<Function> [ <Offset> ]]] |
Quick Samples
The following are typical scenarios with their corresponding formula and results:
| To Get… | Formula | Result |
| Yesterday | day-1 | The previous day relative to the current date. |
| Tomorrow | day+1 | The next day relative to the current date. |
| The current quarter and today | quarter, day | A set of time periods consisting of the current day and current quarter |
| Last 10 days | day:day-9 | A 10 day range including today. |
| Last 10 days (not including today) | day-1:day-10 | A 10 day range NOT including today. |
| Same day last year | (year-1).day | Parallel period: days by year. |
| Same quarter last year | (year-1).month | Parallel period: Quarter by year. |
| Same range of months last year | (year-1).(month-5): (year-1).(month) | Set of parallel periods last year to the last six months. |
| Same range of months to date for last year | (year-1).firstmonth: (year-1).month | From the first month of last year to the month parallel to the current month this year. |
| Year to date | yeartodate | A single time period representing the aggregation of values from the beginning of the year to the last completed period. The period corresponds to the most granular time period defined for the data source. |
| Year to date (by month) | yeartodate.fullmonth | A single time period representing the aggregation of values from the beginning of the year to the last completed month. |
| Year to date (by day) | yeartodate.fullday | A single time period representing the aggregation of values from the beginning of the year to the last completed day. |
| Parallel year to date | yearToDate-1 | The aggregation of the same set of default time periods completed in the current year except for the prior year. |
| Parallel year to date (by month) | (yearToDate-1).fullMonth | The aggregation of the same set of months completed in the current year except for the prior year. |
General Conventions
- TI Formula is not case sensitive
- White-space is ignored
- Use parenthesis whenever you use a time period with an offset together with a function.
- Offsets can be defined on any standard and “to date” time period.
- The current period is assumed when no offset is specified.
Syntax Elements
TI Formula consists of Operators, Periods and Functions. The formula is intentionally simple yet robust.
Operators
The following operators can be used in TI formula expressions.
| Operator | Use |
| “.” | The period or “dot” operator delimits time periods from functions. The part before the dot always corresponds to the time period. The part after the dot always corresponds to the time period function. Example: year . firstMonth |
| “+” or “-” | The plus and minus operators are used to determine the offset relative to the current date. Use the minus sign to specify time periods in the past. Use the plus sign to specify time periods in the future. Example: day - 1 |
| “()” | Parenthesis are used to group a time period and its offset when used together with a function. This is required. Example: ( year-1 ) . firstMonth: ( year-1 ) . month |
| “,” | Comas are used to delimit multiple time periods in the same formula. Example: year , Quarter , Month , day |
| “:” | The colon operator specifies a range of time periods. Example: day : day-4 |
Standard Time Periods
Standard time periods supported in PPS include:
| Period | Example |
| Year | Year-1, year+2 |
| Quarter | quarter, Quarter-4 |
| Month | Month-2 |
| Week | Week-51 |
| Day | Day-9, day+2 |
| Hour | Hour-12 |
| Minute | minute-30 |
| Second | second+5 |
Standard Time Period Functions
Time period functions can be applied to any standard time period using the dot operator. The functions available in PPS include:
| Function | Use |
| FirstQuarter | Specifies the first quarter in a year. |
| LastQuarter | Specifies the last quarter in the year |
| FirstMonth | Specifies the first month in a year or quarter. |
| LastMonth | Specifies the last month in a year or quarter. |
| FirstWeek | Specifies the first week in a year, quarter, or month. |
| LastWeek | Specifies the last week in a year, quarter, or month. |
| FirstDay | Specifies the first day in a week, month or higher time periods. |
| LastDay | Specifies the last day in a week, month or higher time periods. |
| FirstHour | Specifies the first hour in a day, week or higher time periods. |
| LastHour | Specifies the last hour in a day, week or higher time periods. |
| FirstMinute | Specifies the first minute in an hour, day or higher time periods. |
| LastMinute | Specifies the last minute in an hour, day or higher time periods. |
| FirstSecond | Specifies the first minute in an minute, hour, or higher time periods. |
| LastSecond | Specifies the last minute in an minute, hour, or higher time periods. |
Periods-To-Date
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 | Use | Works with… |
| YearToDate | Specifies a time period from the beginning of the year to the current period. | FullQuarter functions or lower. |
| QuarterToDate | Specifies a time period from the beginning of the quarter to the current period. | FullMonth functions or lower. |
| MonthToDate | Specifies a time period from the beginning of the month to the current period. | FullWeek functions or lower. |
| WeekToDate | Specifies a time period from the beginning of the week to the current period. | FullDay functions or lower. |
| DayToDate | Specifies a time period from the beginning of the day to the current period. | FullHour functions or lower. |
| HourToDate | Specifies a time period from the beginning of the hour to the current period. | FullMinute functions or lower. |
| MinuteToDate | Specifies a time period from the beginning of the minute to the current period. | FullSecond function. |
Period To Date Functions
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:
| Function | Use | Works with… |
| FullQuarter | Specifies that the period to date should include up to the last full quarter. | YearToDate |
| FullMonth | Specifies that the period to date should include up to the last full month. | QuarterToDate periods of higher. |
| FullWeek | Specifies that the period to date should include up to the last full week. | MonthToDate periods of higher. |
| FullDay | Specifies that the period to date should include up to the last full day. | WeekToDate periods of higher. |
| FullHour | Specifies that the period to date should include up to the last full hour. | DayToDate periods of higher. |
| FullMinute | Specifies that the period to date should include up to the last full minute. | HourToDate periods of higher. |
| FullSecond | Specifies that the period to date should include up to the last full second. | MinuteToDate periods of higher. Note this is included for consistency, though technically this period is not needed. |
Parallel Period To Date
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.
YearToDate-1
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.
(YearToDate-1).FullMonth
Stephen Handy is a Program Manager on the PerformancePoint Services SharePoint BI team.
Comments
Anonymous
July 07, 2010
I am using PPS 2010 time intelligence to create a zoom filter which list 1m. 3m, up to 10 year. So I use 1m = Month:Month-1 similarly for 5y = Month:(year-5).Month it works. But the problem is with 10 year like 10y = Month:(year-10).Month this give a set of current month to last future month. I am assuming there is something for year -10 in pps like decade or something. Can you please suggest an alternative. Thanks.Anonymous
January 04, 2011
The comment has been removedAnonymous
April 12, 2011
Me also facing the same problem, when I type "MonthToDate" or "YearToDate" then preview pane shows "No Result" why this is happening any idea?Anonymous
May 17, 2011
i want a powerpoint presentation about time intelligence to use in my project of inglishAnonymous
June 17, 2013
Its because you might be using a wrong date in "Reference Date". Use current date.Anonymous
January 22, 2014
Can we incorporate conditional logic into a TI Expression? Our Finance Dept closes the month on the 15th. Say, the current date is Dec 10 2013, which means the previous month (Nov 2013) is not closed yet. So, my YTD formula should include months up to the last closed month, which is Sept 2013. On Dec 16th, month Nov will be closed so I can include Nov in my YTD formula. Any ideas?