Handling missing data for time dimensions

I have a use-case where I want to see SalesQuota by month. In my database, I might not have rows for a particular month in which case I want to map it to a default value (like 0, for example).

The SQL generated in MetricFlow does a simple group by which only displays the sales quota for the months that are existing in the table but does not display 0s for months that do not exist as shown below.

  DATE_TRUNC('month', QuotaDate) AS metric_time__month
  , SUM(SalesQuota) AS sum_sales_quota
FROM AdventureWorks2019.SALES.m_salespersonquotahistory sales_person_quota_history_src_7
  DATE_TRUNC('month', QuotaDate)
ORDER BY metric_time__month

From what I understand, time isn’t considered a first-class citizen when querying a particular measure, instead we pick up what is there in the data and then show it.

If I were to visualize the salesquota over time i.e., over several months, I would necessarily want to view all the months data in the graph with 0s placed wherever there is missing data.

I am not a customer of dbt Semantic layer, I would like to know if this use-case is handled in any way at a higher level or can it be handled in the MetricFlow itself?

Here is an example:

In AdventureWorks2019 schema, we have SalesPersonQuotaHistory

version: 2
  - name: sales_person_quota_history
    description: |
      sales person quota history
    model: ref('m_salespersonquotahistory')
      agg_time_dimension: quota_date
      - name: sales_person_id
        type: foreign
      - name: rowguid
        type: primary
        expr: (BusinessEntityID, QuotaDate)
      - name: quota_date
        type: time
        expr: QuotaDate
          time_granularity: month
      - name: sum_sales_quota
        description: sales quota
        agg: sum
        expr: SalesQuota

  - name: sum_sales_quota
    description: Sales quota.
    type: simple
    label: sum_sales_quota
      measure: sum_sales_quota


select * from Sales.SalesPersonQuotaHistory

Either handle it on the reporting tool (this is a common case so your tool should support it) or add 0’s to your dataset.

Note: @Jack Nguyen originally posted this reply in Slack. It might not have transferred perfectly.