Working With Time

Last updated on 2025-04-29

Overview

There are two ways that time settings can be applied in xP&A:

At Formula Level

The key concept to understand about working with time in xP&A is the timestep index system that can be applied in variables in formulas.

At Model Level

Besides this, you can define certain time settings for each model, which include:

  • Granularity
  • Date range
  • Last actual date
  • Relative dates
  • Fiscal year settings

 

In addition to the time settings affecting the calculations of your data, you have the option to use a time filter in the spreadsheet or dashboard view to merely define the time period you want to look at.

Time Settings at Formula Level
Timestep Index in Variable Modifiers

The time step index allows you to modify variables in formulas, e.g. refer to the value of a variable in three months. This is done using Time modifiers in formulas.

 

The time step index always starts at 0, not 1. So 0 corresponds to the first time step of the model (e.g. Jan'22), 3 corresponds to the fourth time step (Apr'22), and so on.

Shows the timestep index applied in a formula Timestep in a formula
Date function

The Date function converts an input in Date format to the appropriate timestep in a model to allow further calculations. For more information in the functions for Time, see Functions.

 

Shows the 'Date' function used in a formula. Date function
Time-Dependent Helper Variables

In addition, xP&A also has various in-built helper variables (like date, month, year, or lastActualDate) that make it easy to work with time. 

 

Shows the helper variable 'month' used in a formula. Helper variable for time
Time Settings at Model Level

You can also adjust your time settings at the level of each model. Proceed as follows:

  1. Click the gear icon to open the Model settings.
  2. Adjust the options as necessary.
    For a description of the options see the following sections.
  3. Click Apply changes.
  4. In the Apply changes dialog, choose if you want to apply the changes to the current model only or to all linked models.
Options

The following options are available for time settings:

Shows the options that can be edited in the 'Time settings' section for a model. Time settings for a model

Option

Description

Granularity

Granularity refers to the level of detail in which data of a model are organized The following granularity levels exist:

  • Daily
  • Weekly
  • Monthly
  • Quarterly
  • Yearly
  • None

 

For more information on this topic, see Different Time Granularities.

If you change the granularity of a model, you will no longer be able to compare with prior versions.

Model date range

The date range for which the model is calculated.

Last actual date

The last actual date is a model-level setting that represents your latest month (or week if in a weekly model) of "actual" historic data.

To determine your Last actual date, activate this option and select the desired month to be used as last actual date.

 

  • Use this option in a model's time settings only if you enter your actual "historic" data manually. 
  • If you import your actual data using a data integration, this option described here is not used. In this case, the last actual date is set using the intergration via a data source.
  • There is also a helper variable available for the last actual date. For more information, see Helper variables.

Use relative dates

Activate this option, if you want to use relative dates for your model.

Relative dates refer to time periods that are not tied to specific calendar dates but are instead defined in relation to a current or reference point. For example, Year 1 instead of 2020.

Relative dates allow for comparisons across time periods, such as comparing data from the current month to the previous month or year.

Examples:

  • Retention rate: You probably will not use January, February and March for a retention rate, but rather use a customer's first, second, and third month.
  • Sales person (or other productivity driven role) ramp time: When you add a new sales person you probably don't want to assume that they will be 100% productive in their first month but rather that they ramp up at some percentage over time.

 

For more information on relative dates, see Relative Time for Variables.

Start relative dates from "0" / Start from "0"

Activate if the first period to be considered is Period 0.

Use fiscal years

Activate this option if you also want to use a fiscal year for your model and select the Start month and if you want to have your fiscal year label be based on the end of year or start of year.

Your fiscal year settings will affect the column headers in the spreadsheet, as well as any quarterly and yearly roll-up columns).

Example:

If your fiscal year runs from April 1 to March 31, then you would set your start month to be April. 

If you chose the label to be based on the end of the year, i.e. the fiscal year from April 1, 2024 to March 31, 2025 would be labelled "FY24".

 

Shows how the financial year is displayed in the header of the columns, Fiscal year in the column header

Highlight today in spreadsheet

Activate this option to highlight the current timestep in the spreadsheet view.

Using The Time Filter in a Spreadsheet or Dashboard

You can easily adjust the time period you are looking at across the spreadsheet and dashboard by adjusting the time filter in the top-right corner:

Shows the window for adjusting the time filter for a spreadsheet. Highlighted in the area to switch between spreadsheet and dashboard, the button to open the time filter and the window to adjust the time period a user wants to look at Time filter in the spreadsheet

You can choose between the following options for adjusting the time filter:

  • Enter a custom time filter using the calendar:

    Custom time filter
  • Select a dynamic suggested option (e.g. Actuals, Current year, Last 3 months of actuals, Forecast), which will automatically update the visible time range based on changes to either the Last Actual Date, or calendar time.
    Example: if your time filter is set to Last 3 months of actuals, and you roll forward your model's Last actual date, the filter will automatically adjust for the latest 3 months of actuals.


    Suggested time filter

     
  • The time filter setting does not change the model time settings or underlying calculations, it simply filters for the time range you have selected.
  • Dashboard viewers (who do not have edit access to the underlying model) can easily toggle the period they are looking at by themselves (although the default period will be set by model editors).