Implementing fiscal year or fiscal quarter dimensions

Businesses and governments use the concept of a fiscal year (opens in a new tab) for the convenience of their accounting, budgeting, and reporting. Often, the fiscal year would not align with the calendar year (January 1 to December 31). In that case, the fiscal year may either lag or lead by a number of months.

Use case

We'd like to analyze business metrics by attributing them to specific fiscal years and quarters within those years. In this recipe, we'll be using Australia (opens in a new tab) as an example: in this country, the "financial year" begins 6 month earlier than the calendar year, i.e., FY 2024 started on July 1, 2023.

Data modeling

Let's define a couple of auxillary dimensions (fiscal_year_internal and fiscal_quarter_internal) that would translate the time dimension into numeric values of the fiscal year and fiscal quarter, respectively. Make sure to adjust the calculations in these dimensions if your fiscal year is defined differently. Also, you can see that these dimensions are defined as private so they are not visible to end users.

Then, define a string dimension (fiscal_quarter) that would format the fiscal quarter value and expose it to end users:

cubes:
  - name: fiscal
    sql: >
      SELECT '2024-01-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-02-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-03-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-04-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-05-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-06-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-07-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-08-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-09-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-10-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-11-15T00:00:00.000Z'::TIMESTAMP AS timestamp UNION ALL
      SELECT '2024-12-15T00:00:00.000Z'::TIMESTAMP AS timestamp
 
    dimensions:
    - name: timestamp
      sql: timestamp
      type: time
 
      # TODO: Adjust to your fiscal calendar
    - name: fiscal_year_internal
      sql: "EXTRACT(YEAR FROM {timestamp} + INTERVAL '6 MONTH')"
      type: string
      public: false
 
      # TODO: Adjust to your fiscal calendar
    - name: fiscal_quarter_internal
      sql: >
        CASE
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 7 AND 9 THEN 1
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 10 AND 12 THEN 2
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 1 AND 3 THEN 3
          WHEN EXTRACT(MONTH FROM {timestamp}) BETWEEN 4 AND 6 THEN 4
        END
      type: string
      public: false
 
    - name: fiscal_quarter
      sql: >
        'FY' || {fiscal_year_internal} || '-Q' || {fiscal_quarter_internal}
      type: string

Result

Now you can use the fiscal_quarter dimension in your queries and get desired result:

Implementing Entity-Attribute-Value Model (EAV)Passing dynamic parameters in a query