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.