Metric Views Window Period to Date not working

[jroots]

The documentation gives an example of a period to date measure. The intention is to give the sum (in this example) of orders in the current year to date. However, what actually happens is that the measure returns the result for the most recent period for which there are entries. If the current year has no rows, it returns the result for the most recent year which does. (There is a workaround, which is to use a filter with current_timestamp(), but then materialized views don't work.)

See results and measure definition attached. The measure definition is taken directly from the referenced documentation.

[Ashwin_DSA]

Hi @jroots,

On some research, I can see that the YAML in the docs is doing what window measures are defined to do, but the wording is perhaps a bit misleading. In the below example you shared,

range: current means "rows whose order value equals the current row’s value,"...

and semiadditive: last says "when that order dimension (here year and date) isn’t grouped, use the last value in that window.”

When you query MEASURE(ytd_sales) without grouping by year or date, the engine selects the last date from the last year in the data and returns that year-to-date total. If no data for the actual calendar year contains current_date(), it naturally falls back to the most recent year that does have rows.

To get "YTD for the calendar year containing current_date()," you currently have to push the notion of "current year" into the query instead of the metric view definition, for example, by filtering on DATE_TRUNC('year', date) = DATE_TRUNC('year', CURRENT_DATE()). Putting CURRENT_DATE() / CURRENT_TIMESTAMP() directly in the metric view YAML makes the definition invoker-dependent, which is why materialized metric views then stop working (they can’t be reused if the definition depends on evaluation time).

Does this give you a direction?

If this answer resolves your question, could you mark it as “Accept as Solution”? That helps other users quickly find the correct fix.