Analysing Transactional Data Across Time Periods: YoY, R12M, YTD

This article illustrates how to analyse a transactional dataset to compare accumulated metrics across different periods, such as year-to-date (YTD), rolling 12 months, or this year versus the same period last year.

We can also visualise months on the X-axis and layer lines where each line represents a different year. This allows us to observe seasonal patterns - for instance, that January tends to be a busy month, with increasing volumes year on year.

To perform this analysis, we need at least one value field and one date field.

Note: It is helpful to merge your dataset with a Date Sequence block to ensure full date coverage in the target period, accounting for days such as weekends when data points may be missing.

Before embarking on data transformation - please see this article which explains how to compare consecutive periods (weeks, months, quarters, years) by using Omniscope's built-in features:

Visualise consecutive period differences - no calculations required.

Step 1: Preparing the Date Fields

To enable period-based analysis, duplicate the date field and configure the outputs as follows:

• Month field: Set the output date as "M" to display month only (from the field's 3-dot menu!), and change the format to integer.

• Year field: Create another date field duplicate, this time set the output as "yyyy", to display year only and format it as Text.

Step 2: Adding Formula Fields

Add the following calculated fields to support comparative and rolling analyses.

a) Value on This Date Last Year

Retrieves the value from the same date one year earlier (adjustable for different periods if required):

SUBSET_SUM(
  [Value],
  subset([Date], DATEADD([Date], -1, "year"))
)

b) Rolling 12 Months Indicator

Determines whether the record falls within the most recent 12-month period:

IF(
  DATEDIFF([Date], TODAY(), "months") > 12,
  null,
  "R12M"
)

c) Year-to-Date (YTD) Indicator

Identifies whether the record belongs to the current year by comparing it to today's year:

IF(
  DATEUNIT([Date], "year") = DATEUNIT(TODAY(), "year"),
  "YTD",
  null
)

Step 3: Visualising the Results

Once the calculated fields are added, you can create visualisations to track value dynamics over time.
Use year, month, or period slider filters to drill down into specific time frames and explore performance across years, rolling 12-month windows, or YTD progress.

IOZ demo file is attached below - if you download it and import into your Omniscope folder - you will be able to explore the features in more detail.