There are several situations where you might want to get the number of days in a given month.

You might try to do this by hard-coding the number of days for each month into a new dimension with a 12-case `CASE WHEN`

, but then you'd also need to handle leap years, where the number of days in February changes.

A better solution is to write a table calculation that can dynamically account for leap years.

# The Table Calculation

Given a date dimension `${date}`

(where you would replace this placeholder with the name of your actual `date`

field), this calculation returns the number of days in the month of `${date}`

:

extract_days(add_days(-1, date(extract_years(add_months(1, ${date})), extract_months(add_months(1, ${date})), 1)))

This calculation starts with the first day of the next month. The calculation then moves back one day, checks the day of the month, and returns the number of that day.

# The In-Depth Explanation

- First, assume
`${date}`

is January 10, 2020. - Then
`extract_years(add_months(1, ${date}))`

is the year of the next month, which is 2020. Let's call this`year`

. - Similarly,
`extract_months(add_months(1, ${date}))`

returns the month number of the next month, which is 2. Call this`month`

. - Now
`date([year],[month],1)`

returns 2/1/2020, the first day of next month. We'll call this`next_month`

. - Next,
`add_days(-1, [next_month])`

travels one day backwards, to reach the last day of this month, which in our example returns January 31, 2020. Call this`last_day`

. - Finally,
`extract_days([last_day])`

returns the day number of`last_day`

. In this example, this returns 31, the number of days in January!