Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

PerformancePoint Time Intelligence quick reference

Published: October 30, 2012

Summary: Learn which formulas and syntax to use when using Time Intelligence while creating dashboards with PerformancePoint Services Dashboard Designer.

Applies to:  SharePoint Server 2013 Enterprise 

PerformancePoint Services in SharePoint Server 2013 includes advanced functionality that includes Time Intelligence. You can use Time Intelligence to display information for dynamic periods of time, such as year to date or last 30 days, in your reports and scorecards. You can also create dashboard filters that use Time Intelligence.

When you include Time Intelligence in your dashboards, you use a particular syntax to create formulas that are known as TI formulas.

Read this article for an overview of the different kinds of TI formulas that you can use. For information about how to use Time Intelligence in your dashboards, see the following articles:

Quick samples

Formula structure:

[(]<Period>[Offset>[)][ <Function>[<Offset>]]]

To display 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 (excluding today)

day-1:day-10

A 10-day range NOT including today.

Same day last year

(year-1).day

Current date (month and day) for last year. For example, if the current date were December 10, 2012, then (year-1).day would show information for December 10, 2011.

Same month last year

(year-1).month

Current month for last year. For example, if the current month were December, 2012, then (year-1).month would show information for December, 2011.

Same range of a period of six months last year

(year-1).(month-5): (year-1).(month)

From 18 months ago to one year ago. For example, if the current month were December 2012, then (year-1).(month-5): (year-1).month would show information for the time period ranging from June, 2011 to December, 2011.

Same range of months to date for last year

(year-1).firstmonth: (year-1).month

From the first month of last year up to and including 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 up to and including the last completed period. The period corresponds to the most specific 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 up to and including 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 up to and including 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 time periods last year that is equal to year to date periods for the current year.

General conventions

  • TI formulas are not case-sensitive.

  • White space is ignored.

  • Use parentheses when 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.

  • Use singular nouns, not plurals in your TI formulas. For example, use "month" and not "months."

Syntax elements

The TI formulas consist of operators, periods and functions. The formula is intentionally simple yet robust.

Operators

Operator Use

“.“

The period or “dot” operator delimits time periods from functions. The string in front of the dot always corresponds to the time period. The string following 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

“()”

Parentheses are used to group a time period and its offset when you use them together with a function. Required.

Example:  (year-1).firstMonth:(year-1).month

“,”

Commas 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 PerformancePoint Services include the following:

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 PerformancePoint Services include the following:

Function Use

FirstQuarter

Specifies the first quarter in a year.

LastQuarter

Specifies the last quarter in a 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 larger time period.

LastDay

Specifies the last day in a week, month, or larger time period.

FirstHour

Specifies the first hour in a day, week, or larger time period.

LastHour

Specifies the last hour in a day, week, or larger time period.

FirstMinute

Specifies the first minute in an hour, day, or larger time period.

LastMinute

Specifies the last minute in an hour, day, or larger time period.

FirstSecond

Specifies the first minute in a minute, hour, or larger time period.

LastSecond

Specifies the last minute in a minute, hour, or larger time period.

Period-to-date formulas

Period-to-date formulas are new in PerformancePoint Services. Period-to-date calculations aggregate all the time periods to date up to the last completed full period. Incomplete time periods are automatically excluded. By default, they are evaluated to the lowest degree of precision in the data source. For example, if the most specific 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; these automatically include incomplete periods.) PerformancePoint Services supports the following period-to-date time periods:

Period Use

YearToDate

Specifies a time period from the beginning of the year to the current period.

QuarterToDate

Specifies a time period from the beginning of the quarter to the current period.

MonthToDate

Specifies a time period from the beginning of the month to the current period.

WeekToDate

Specifies a time period from the beginning of the week to the current period.

DayToDate

Specifies a time period from the beginning of the day to the current period.

HourToDate

Specifies a time period from the beginning of the hour to the current period.

MinuteToDate

Specifies a time period from the beginning of the minute to the current period.

Period-to-date functions

Period-to-date functions enable you to control the level of detail for time periods to include. If, for example, you want to show the year-to-date including up to the last completed full month, you can add a full-period function. PerformancePoint Services supports the following full-period functions:

Function Effect

FullQuarter

Specifies that the period to date should include up to the last full quarter.

FullMonth

Specifies that the period to date should include up to the last full month.

FullWeek

Specifies that the period to date should include up to the last full week.

FullDay

Specifies that the period to date should include up to the last full day.

FullHour

Specifies that the period to date should include up to the last full hour.

FullMinute

Specifies that the period to date should include up to the last full minute.

FullSecond

Specifies that the period to date should include up to the last full second.

Parallel period-to-date

You can use Time Intelligence to compare values for a set of time periods for the current year to an equivalent set of time periods for the previous year. For example, suppose that the current year to date includes January–July of 2012. You may want to compare this value to the same set of time periods for the previous year; in this case January–July 2011. This is easy to do by using TI formulas. Simply add an 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 applied additionally. Remember to use parentheses in this case.

(YearToDate-1).FullMonth

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.