Calculate last day of month

Sometimes you need to calculate how many days a certain month – like the current month. With some nested date-calculations this isn’t that hard to calculate…

First the complete formula:

<strong>DATEPART('day',DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,TODAY()))))</strong>

This calculations uses 3 different Tableau functions: DATEPART, DATEADD and DATETRUNC:

Step 1: Add 1 month to the date

Add 1 month to the date of today; this date will never exceed the last day of next month (adding 1 month to Jan 31 2019 will result in Feb 28 2019)

2017-05-29 -> 2017-06-29

DATEADD('month',1,TODAY())

Step 2: Get the first day of that next month

Truncate “today +1 month” to the first of the month: 2017-06-29 to 2017-06-01

DATETRUNC('month',DATEADD('month',1,TODAY())

Step 3: Substract one day

Substract one day of ‘the first of the month truncated to month 2017-06-01 -> 2017-05-31

DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,TODAY())))

Step 4: Day of month (optional)

DATEPART('day',DATEADD('day',-1,DATETRUNC('month',DATEADD('month',1,TODAY()))))

The day-number of that date is the number of days of this month

2017-06-01 > 31

Instead of the number of days of this month you can of course use any date – just replace TODAY() by the [Date] field in your datasource.