In a semantic model, you can use the TODATE function to calculate the period to date value for a measure.

This example shows a report with the measure Dollars QTD, the Quarter To Date version of the Dollars measure.

In the example, Dollars QTD for Month 2008/05 is the sum of Dollars for 2008/04 and 2008/05. Dollars QTD is the sum of the values for all the query grain periods (month) for the current time series grain period (quarter). The accumulation starts over for the next quarter.

In the example, the Dollars QTD measure is derived from the Dollars measure.

In Expression Builder, the TODATE function uses the following format:

ToDate(<<Measure>>, <<Level>>)

<<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 time series grain you want to use. In this example, you select Quarter from your time dimension.

Using this function format, you can create the following expression for the measure:

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

The query grain is specified in the query itself at run time. For example, this measure can display Quarter To Date at the Day grain, and accumulates up to the end of the Quarter.

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