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.
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
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
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):