Function Date Calendars

The Function Picker allows you to filter the query dynamically using a date function that references the "latest date." Which date is the latest depends on your model:

  • If your model includes dates up to and including the current date, then the latest date is today.
  • If your model only includes dates from the past, then the latest date is the latest date that we have data for. If we have data from 2010 only, then the latest date is in 2010.
  • If your model includes future dates, then the latest date is the last of the future dates. If we have data to 2050, then the latest date we have data for is in 2050.

The example below (YTD) filters the query by the year to date. This means that, if the latest date you have data for is today, Pyramid will select the data for this year up to and including today:

Important: The Function picker follows the same logic as the Formula picker, but with two key differences: the reference date is always the latest date and no Granularity selection is required.

Functions

The functions that can be selected from this dropdown list enable the dynamic selection of a particular date or date range relative to the latest date:

  • Current <Period>: Select the current date, month, semester, quarter, or year in the slicer.
  • First <Period>: Select the first date, month, semester, quarter, or year in the slicer.
  • Last <Period>: Select the last date, month, semester, quarter, or year in the slicer.

    • Disambiguation: The term "last" here refers to the latest period, not the last completed period; it aligns with the phrase "in the last month" rather than "last month" in colloquial English. For example, if today is January 15, "last month" in this sense means January, not December.

  • nTD: Select the week, month, quarter, or year to date in the slicer.
  • Last # <Period>: Select the last available dates. Returns a date range.
  • Next # <Period>: Select the next dates after the last available date. Returns a date range.
  • Lag # <Period>: Select the single date that is earlier than the selected date by the specified number of periods. For example, the date that is 5 days earlier than the current date.
  • Lead # <Period>: Select the single date that is later than the selected date by the specified number of periods. For example, the date that is 5 days later than the current date.