Weekly granularity and multi-stage calculations

[igorlukanin]

That looks like a great feature!

Testing it, I have what I think is an unexpected behaviour when I select weekly granularity.
On a measure summing revenues, the result for revenue_prior_year on the 1st week of 2025 does not match the result for revenue for the 1st week of 2024.

I can get the expected behaviour if I set

- name: revenue_last_year_weekly
  sql: revenue
  type: number
  rolling_window:
      trailing: 52 week
      leading: -51 week
      offset: start 

date	revenue	revenue_last_year_weekly
2024-01-01 W1	409,001	NA
...	...	...
2024-12-30 W1	230,002	409,001

But with the recommended implementation of multi-stage calculations, the numbers don't match anymore.

- name: revenue_prior_year
  multi_stage: true
  sql: "{revenue}"
  type: number
  time_shift:
     - time_dimension: date
        interval: 1 year
        type: prior

date	revenue	revenue_prior_year
2024-01-01 W1	409,001	NA
...	...	...
2024-12-30 W1	230,002	558,208

That approach returns matching results when the time dimension is set at yearly or monthly level, but not for weekly granularity.
I could keep the first one but it'd mean to have a dedicated measure for each time dimension granularity which is not ideal.
By the way, the first approach stops working when setting cubejs_tesseract_sql_planner = true. It return the following error:

SQL compilation error: syntax error line 19 at position 6 unexpected 'VALUES'.

Am I misinterpreting the expected behaviour? Is there a way we can align comparison vs last year on weekly granularity too?

Originally posted by @AlexisBocuze in #8486