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

1. First, assume ${date} is January 10, 2020.
2. Then extract_years(add_months(1, ${date})) is the year of the next month, which is 2020. Let's call this year.
3. Similarly, extract_months(add_months(1, ${date})) returns the month number of the next month, which is 2. Call this month.
4. Now date([year],[month],1) returns 2/1/2020, the first day of next month. We'll call this next_month.
5. 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.
6. Finally, extract_days([last_day]) returns the day number of last_day. In this example, this returns 31, the number of days in January!