dbt metrics time grains

Help
1

The problem I’m having

I would like to introduce a metric that is calculated on both: beginning of the week, beginning of the month, end of the week, end of the month time grains.

The context of why I’m trying to do this

I’m trying to do this because our internal definition of the metric can vary depending on the team that is using it. Finance teams are only interested in headcount on the finale day of each period (week, month, quarter) while other teams require taking the whole month into consideration.

What I’ve already tried

I’ve built the mart model with the following fields:
date, is_active, user_id, day_is_first_of_week, day_is_first_of_month, day_is_last_of_week, day_is_last_of_month

Some example code or error messages

Here I’m not sure how to define grain properly as I assume it will calculate count of distinct user_id within the week, month but I would actually need it to consider the end dates of each period as well.

Should I be creating separate metrics based on multiple mart models or can my requirements still be achieved through one metric?

2

You’re on the right track! Instead of adding those properties to your mart, you should create a custom calendar table.

So your mart would have the columns

date user_id is_active
2023-01-01 123 true
2023-01-01 456 true
2023-01-02 123 true
2023-01-02 456 false

And your calendar table would be:

date_day date_week date_month date_quarter date_year is_first_of_month is_last_of_month
2023-01-01 … … … … true false
2023-01-02 … … … … false false
2023-01-31 … … … … false true

These dimensions will be available without being added to the metric, see dimensions from calendar tables in the readme for instructions on how to configure them.