MetricFlow knows calendar grains: day, week, month, quarter, year. A fiscal year that starts on 1 April, a retail 4-4-5 calendar or a fiscal week are custom granularities: you compute them as extra columns of the time spine, declare them in its YAML, and query them like any other grain.

Copy and paste

The time spine with fiscal columns (DuckDB/Postgres SQL; one row per day):

-- models/time_spine_daily.sql
with days as (
    {{ dbt.date_spine('day', "cast('2025-01-01' as date)", "cast('2026-06-01' as date)") }}
),
spine as (
    select cast(date_day as date) as date_day from days
)
select
    date_day,
    -- fiscal year starting on 1 April: shift back 3 months, truncate, shift forward
    cast(date_trunc('year', date_day - interval 3 month) + interval 3 month as date) as fiscal_year
from spine

On Snowflake, write the expression as dateadd(month, 3, date_trunc('year', dateadd(month, -3, date_day))).

Its YAML:

models:
  - name: time_spine_daily
    time_spine:
      standard_granularity_column: date_day
      custom_granularities:
        - name: fiscal_year
          column_name: fiscal_year
    columns:
      - name: date_day
        granularity: day
      - name: fiscal_year

Query it:

mf query --metrics revenue,order_count --group-by metric_time__fiscal_year
metric_time__fiscal_year      revenue    order_count
--------------------------  ---------  -------------
2024-04-01T00:00:00               310              3
2025-04-01T00:00:00              1370             12
2026-04-01T00:00:00               550              4

Each fiscal year is labelled by its first day: the row of 2025-04-01 is April 2025 to March 2026. January to March 2025 belong to the fiscal year that started in April 2024.

How custom granularities work

MetricFlow joins the metric’s dates to the time spine on date_day and groups by the column you named. The column can hold any value that is the same for every day of the period; the first day of the period, as above, sorts correctly and reads well. Names are lowercase.

The grain is available wherever a time grain is: metric_time__fiscal_year, order__order_date__fiscal_year, and in filters as {{ TimeDimension('metric_time', 'fiscal_year') }}.

What works and what doesn’t

Tested with dbt Core 1.12 and MetricFlow 0.213:

Use Result
Group by metric_time__fiscal_year works
Group another time dimension by it works
Filter on TimeDimension('metric_time', 'fiscal_year') works
Derived metric with offset_window: 1 year, grouped by fiscal year works: 240 last year against 550
Cumulative metric with grain_to_date: fiscal_year refused at parse

The last one is the fiscal YTD metric you would write first, and dbt stops with:

Invalid time granularity found in `grain_to_date`: 'fiscal_year'. Custom granularities are not
supported for this field yet.
...
Parsing Error
  Semantic Manifest validation failed.

Fiscal year to date without grain_to_date

Group revenue by fiscal year and stop the days at the reference date:

mf query --metrics revenue --group-by metric_time__fiscal_year \
  --where "{{ TimeDimension('metric_time', 'day') }} <= '2026-05-15'"

The 2026-04-01 row then returns 250: the orders of 8 April, 2 May and 15 May, the same fiscal YTD as in the Snowflake and QuickSight versions of this guide. For a line of fiscal YTD by month, compute the running total in the BI tool, partitioned by fiscal year, or build it in a dbt model until grain_to_date accepts custom granularities.

Fiscal quarters

A fiscal year that starts in April has quarters that coincide with calendar quarters (April–June is both Q2 and fiscal Q1), so metric_time__quarter already groups correctly; only the names differ. When the fiscal year starts in a month that doesn’t open a calendar quarter, such as February, add a fiscal_quarter column built the same way with date_trunc('quarter', ...) and declare it as a second custom granularity.

Tested with dbt Core 1.12 and MetricFlow 0.213 on DuckDB.

References