In a semantic model, you can use the AGO function to calculate the value of a measure a set number of periods ago.

This function is useful for comparisons such as Dollars compared to Dollars a Quarter Ago. The value of Dollars Qago for month 2008/08 equals the value of Dollars for month 2008/05.

This example shows values for the Dollars and Dollars Qago measures.

In the above example, the Dollars Qago measure is derived from the Dollars measure.

In Expression Builder, the AGO function has the following template:

Ago(<<Measure>>, <<Level>>, <<Number of Periods>>)

<<Measure>> represents the logical measure column that you want to derive from. In this example, you select the measure "Dollars" from your existing logical fact tables.

<<Level>> is the optional time series grain you want to use. In this example, you select "Quarter" from your time dimension.

<<Number of Periods>> is the size of the offset, measured in the grain you provided in the <<Level>> argument. For example, if the <<Level>> is Quarter and the <<Number of Periods>> is 2, the function displays dollars from two quarters ago.

Use this function template to create an expression for a One Quarter Ago measure, as follows:

Ago("Sales"."Base Measures"."Dollars" , "Sales"."Time MonthDim"."Quarter" , 1)

The <<Level>> parameter is optional. If you don't want to specify a time series grain in the AGO function, the function uses the query grain as the time series grain.

For example, you could define Dollars_Ago as Ago(Dollars, 1). Then, you could perform the following logical query:

SELECT Month, Dollars, Dollars_Ago

The result is the same as if you defined Dollars_Ago as Ago(Dollars, Month, 1), or you could perform the following logical query:

SELECT Quarter, Dollars, Dollars_Ago

The result is the same as if you defined Dollars_Ago as Ago(Dollars, Quarter, 1).

See Logical SQL Reference Guide for Oracle Business Intelligence Enterprise Edition.