DbT Semantic Layer: Average Monthly Value

Help
,
1

Currently trying to create a metric in DbT which corresponds to the average monthly value for stores.

With this in mind, I’m creating a derived metric which picks up the base measures and creates the corresponding ratio:

metrics:
  - name : average_store_monthly_revenue
    label: Average Store Monthly Revenue
    type: derived
    type_params:
      expr: revenue/stores
      metrics:
        - name: revenue
        - name: stores

The question is, how to aggregate this at the monthly level? Meaning, so we can actually get the monthly average?

I have tried setting the time_granularity parameter but unfortunately, it is only available at the Dimension level.

metrics:
  - name : average_store_monthly_revenue
    label: Average Store Monthly Revenue
    type: derived
    type_params:
      time_granularity: month
      expr: revenue/stores
      metrics:
        - name: revenue
        - name: stores

There is, AFAIK, no option for grouping the data at the monthly level directly on metrics. How would I go about doing this?

How about if we want to expand this metric to consider the Store’s last 6 months of revenue?