There are several cases where we might want to get the number of days in a given month. This could be done with a 12-case `CASE WHEN`

, but then leap years for February must be considered.

Instead, we can write a simple table calculation to do it.

# The Table Calculation

Given a date dimension ${date}, this calc 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 next month, then moves back one day, and finally returns that day of month. Following is a more in-depth explanation in case you’re interested.

# The Explanation

- First assume
`${date}`

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

will be the year of next month, which is 2017. Let’s call this [year]. - Similarly,
`extract_months(add_months(1, ${date}))`

will return the month of next month, which is 2. Call this [month]. - Now
`date([year],[month],1)`

will return 2/1/2017, 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, 2017. Call this`last_day`

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

returns the day part of`last_day`

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

## Comments

0 comments

Article is closed for comments.