Metabase Date Filter with Variable

Question

Looking for a little Metabase assistance. I'm trying to create a Dashboard with several charts/tables that are all dependent on one date filter where the user can select the month (not specific days). This is the first chart (cumulative net revenue of the current month compared to previous month and last year). I've gotten this query to work if I set the variable type to "Date".

SELECT DISTINCT
DATE_TRUNC('month', day)::date as month,
EXTRACT(DAY FROM day) as day_of_month,
SUM(net_sales) OVER (PARTITION BY DATE_TRUNC('month', day) ORDER BY EXTRACT(DAY FROM day) ASC) AS cumulative_net_revenue
FROM
    "public"."summary_shopify_20240925153955"
WHERE
    -- Selected month
    (EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM {{date}}::date) 
      AND EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM {{date}}::date))

    -- Previous month
    OR (EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM {{date}}::date - INTERVAL '1 MONTH') 
     AND EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM {{date}}::date - INTERVAL '1 MONTH'))

    -- Same month last year
    OR (EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM {{date}}::date) 
     AND EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM {{date}}::date - INTERVAL '1 YEAR'))
ORDER BY 1,2

However, when I update the query to use the "Field Filter" variable type (see below), I continually get errors usually related to this "ERROR: function pg_catalog.date_part(unknown, boolean) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts."

SELECT DISTINCT
DATE_TRUNC('month', day)::date as month,
EXTRACT(DAY FROM day) as day_of_month,
SUM(net_sales) OVER (PARTITION BY DATE_TRUNC('month', day) ORDER BY EXTRACT(DAY FROM day) ASC) AS cumulative_net_revenue
FROM
    "public"."summary_shopify_20240925153955"
WHERE 
    -- Use the field filter for "day"
    {{date}} -- This will apply the filter on the "day" field
    OR (
        -- Previous month
        EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM ({{date}}::date - INTERVAL '1 MONTH'))
        AND EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM ({{date}}::date - INTERVAL '1 MONTH'))
    )
    OR (
        -- Same month last year
        EXTRACT(MONTH FROM day) = EXTRACT(MONTH FROM ({{date}}::date))
        AND EXTRACT(YEAR FROM day) = EXTRACT(YEAR FROM ({{date}}::date - INTERVAL '1 YEAR'))
    )
ORDER BY 1,2

Including a couple screenshots to show the variable input, a couple sample rows of the table, and confirmation "day" is of the date type.

Any help is greatly appreciated