How do I calculate the number of days in a month?

jcolucci · January 9, 2025, 12:12am

robdhondt · January 9, 2025, 1:31am

To calculate the number of days in a month, you can:

Truncate the original date to the first day of its month using truncDate('MM', Date).
Add one month to that truncated date using addDateTime(1, 'MM', truncDate('MM', Date)).
Calculate the difference in days between these two dates.

That looks like this:

dateDiff(
  truncDate('MM', Date),
  addDateTime(1, 'MM', truncDate('MM', Date)),
  'DD'
)

jcolucci · January 10, 2025, 1:26pm

Hi Rob, good morning!
This solution is not working for Quicksight.

robdhondt · January 10, 2025, 2:51pm

You need to use days_in_month as a GROUP BY or take the MAX instead of the default SUM if you are going to use it as VALUE

Example:

jcolucci · January 14, 2025, 1:12pm

Good morning!

Thanks, Rob! The problem has been solved.