Creating a data module for relative date analysis

To enable relative date analysis, you need to create a data module where business data is associated to a calendar.

In this data module, at least one date column must be associated to a calendar, and at least one measure column must be associated to the date column. This association is done by using the column property Lookup reference.

Before you begin

The sample calendars must be available.

About this task

You can create a new data module from scratch, or add relative date capabilities to an existing data module.

Tip: The Team content > Samples > Relative dates folder contains the Boston 311 report and Boston 311 dashboard samples that illustrate the implementation of this feature in a report and dashboard. The Data folder that is included with these samples contains the associated data modules and their source .csv files. You can use these samples as a reference when you create your data module.

Procedure

  1. Create a data module, or open an existing data module.
  2. Verify that your business data sources contain at least one date column and one measure column.
    1. From the date column menu, select Properties > General. Ensure that the Data type property of the column is set to Date.

      If the Data type property is set to Timestamp, you can change the type to Date by using the cast function in the expression editor.

      If the data source is an Excel file or a CSV file, dates in the date column must be formatted with the ISO 8601 notation yyyy-mm-dd.

    2. From the measure column menu, select Properties > General. Ensure that the Usage property of the column is set to Measure.

      If the Usage property is set to Identifier, you can change the property to Measure.

    Tip: If your data module source is linked to its source, which is indicated by the link icon Icon denoting linked module., you need to break the link. Otherwise, the data module is read-only, and you can't modify its properties. To break the link, select the Break link option from the data module menu. However, do not break links in any of the sample calendar data modules.
  3. In the Data module panel, click the Add sources and tables Icon to add a data source icon to add a calendar source, which can be one of the following sources:
    • The sample Gregorian calendar data module in the Team content > Calendars folder.
    • The sample Fiscal calendar data module in the Team content > Calendars folder.
    • One of the sample data modules in the Team content > Calendars > Fiscal calendars folder.
    • The sample Retail calendar_454_2016_2022 data module in the Team content > Calendars folder.
  4. In your business data source that you specified in steps 1 and 2, associate at least one date column to the calendar, and at least one measure column to the date column.
    1. For the date column that you want to associate to the calendar, open Properties, and locate the Lookup reference property. From the Lookup reference drop-down menu, select the name of the calendar source that you added to the data module. If needed, repeat this step for other date columns.

      The relative date filters, such as Prior year, Prior month, MTD, and so on, appear under the date column. To view the full list of filters, see Sample calendars.

    2. For the measure column that you want to associate to the date, open Properties, and locate the Lookup reference property. From the Lookup reference drop-down menu, select the date column to reference. If you defined Lookup reference for multiple date columns, choose the date column that is appropriate for this measure. If needed, repeat this step for other measure columns.
      Tip: To specify the same Lookup reference property for multiple measure columns, multi-select the columns, and set the property.

      A set of date-filtered measures, with the measure name in square brackets, appears under the measure column. For example, Prior year [Revenue], Prior month [Revenue], or MTD [Revenue].

    To use one or more of the date-filtered measures in calculations, you can create the calculations only against the data-module, and not against the tables that contain these measures. The calculations appear at the top of the data module tree.

  5. Save the data module to a folder in Team content.
    Tip: If you add or remove a filter from a calendar data module, the data modules that reference this calendar through the Lookup reference property don't reflect the change until you close and reopen them.

Results

The data module can now be used to create dashboards and reports.