How to calculate the number of days in a month and monthly averages in Looker Studio?
Tricky formula, but outrageously powerful! In this article, we show you how to calculate the number of days in a month in Looker Studio
Want to do proper averages per day, or need to report number of days per month for the finance team, but you’re struggling to make it work? No worries, we’ve got you covered. In this article, we’ll show you how to calculate the number of days in a month and therefore monthly averages in Looker Studio.
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Calculate first day of the month
So, let’s assume you’ve got a data source with a date fields, and entries for at least one days in and all months (if a month has no entry, it won’t return anything hence you won’t see the number of days), something that looks like:
Now, we’re going to create a calculated field dimension to find the first day of the month using the function DATETIME_TRUNC:
CAST(DATETIME_TRUNC(date,MONTH) AS DATE)This will always return the 1st day of the corresponding month of the date!
That’s a good start.
Calculate first day of next month
Now, if we want to calculate the number of days in the month, best way is to add a month to what we’ve just calculated, hence:
CAST(DATETIME_ADD(DATETIME_TRUNC(Date,MONTH), INTERVAL 1 MONTH) AS DATE)If you’ve followed the steps carefully, you should now see this:
Calculate the diff to find the number of days in the month
We’re almost there! Now that we’ve got first day of the month, first day of the next month, a simple DATE_DIFF will give us the number of days in-between, ie the number of days in the month:
DATE_DIFF(CAST(DATETIME_ADD(DATETIME_TRUNC(Date,MONTH), INTERVAL 1 MONTH) AS DATE),CAST(DATETIME_TRUNC(Date,MONTH) AS DATE))And that’s it! We’ve used these formulas in dimension on a table split by Month. In case you’d want to use it as a metric or in a calculated field, you can enclose it with an AVG(…) around to make sure it still works smoothly:
SUM(Impressions)
/
AVG(
DATE_DIFF(
CAST(DATETIME_ADD(DATETIME_TRUNC(Date,MONTH), INTERVAL 1 MONTH) AS DATE)
,
CAST(DATETIME_TRUNC(Date,MONTH) AS DATE)
)
)
PROBLEM SOLVED
Top Looker Studio connectors we love and use on a daily basis (all with free trials): PMA - Windsor - Supermetrics - Catchr - Funnel - Dataslayer. Reviews here and there.
Not sure which one to pick? Have a question? Need a pro to get a project done? Contact us on LinkedIn or by e-mail, and we’ll clear up any doubt you might have.
Communicate and browse privately. Check Proton Mail and Proton VPN
Website hosted by Tropical Server