Create AGO, TODATE, and PERIODROLLING Measures in a Semantic Model

You can build time series measures by creating derived expressions from base measures.

Follow these guidelines when modeling time series functions:

  • You can't derive time series functions from measures that use the fragmentation form of federation. This rule prevents some complex boundary conditions and cross-source assumptions in the query generation and result merging, such as the need to join some time dimension rows from one source to some of the fact rows in a different source. To reduce maintenance and increase accuracy, it's best to create a single base measure, and then derive a family of time series measures from it. For example, start with a base measure, then define variations for month-ago, year-ago, and month-to-date.

  • You must define the unit as a level of the time dimension, so that it can take advantage of the chronological keys built in the time dimension.

For information about how to use time series functions in expressions, see About the AGO Time Series Function, About the TODATE Time Series Function, and About the PERIODROLLING Time Series Function.

  1. On your home page, click Navigator Navigator icon and then click Semantic Models.
  2. In the Semantic Models page, click a semantic model to open it.
  3. Click Logical layer Logical layer iconand double-click the logical table containing the logical column that you want to add a time series function to. Click Columns.
  4. Locate and click the column and click Detail View. Scroll to Sources and click Logical Expression.
  5. Click Open Expression Editor.
  6. In Expression Builder, go to the Function panel and scroll to Time Series Calculations and use these functions to build the expression.
  7. Click Save.