Tableau Date Calculation Cheat Sheet

For some time now, I’ve maintained a cheat sheet for various calculated fields that I find myself using (or sharing) regularly. I recently noticed how many of these calculations were date-related. It occurred to me that it would be nice to have a single resource that contains all of these date calculations, making them available for other to use, without the need for reinvention. So, that’s exactly what I’m going to be sharing here today. My Date Calculation Cheat Sheet contains 46 reusable calculation. In this blog, I’ll provide a brief introduction and some example calculations, then show you how to use them in your own work.

 

Tableau has tons of different date and time-related functions for you to use. I’m assuming a familiarity with many of these already so I won’t be talking about them in any detail. If you’re not familiar with these functions, I recommend the following blog by Carly Capitula of Interworks, which gives a good overview of most of them: Tableau Essentials: Calculated Fields – Date Functions

 

Note: If you want to skip the explanation of the various calculated fields and jump right to the workbook, you can find it here: Date Calculation Cheat Sheet. Just download it and copy the calcs you need.

 

The Calculations

As noted, I’ll be sharing 46 calculated fields. Forty-two of these are intended to return the following for the previous, current, or next week, month, quarter, or year:

 

•  The first and last day of each period

•  The number of days in each period

•  The number of weekdays in each period

 

The remaining 4 calculated fields will give you the following:

 

•  Decade

•  Century

•  Last of a Specific Day in a Month – For example, the last Sunday of the month.

•  Nth of a Specific Day in a Month – For example, the third Wednesday of the month.

 

As the first 42 are all of a common theme, I’m going to share the details of the month calculations here and you can refer to the workbook for week, quarter, and year calculations. Note: These calculations refer to a field called Date which could be any date or parameter in your data.

 

First & Last Day

Let’s start by calculating the first day of the month for the current month (the month of the Date field). To do this, we’re simply going to truncate the date to the first day of the month. Then, since DATETRUNC returns a date/time field, we’ll convert it to a date.

 

Month – Current – First Day

DATE(

    DATETRUNC('month', [Date])

)

 

For the last day, we start by truncating to the first day of the month. Then we add a month, giving us the first day of next month. Then we subtract a day to get the last day of the current month.

 

Month – Current – Last Day

DATE(

    DATEADD('day', -1,

        DATEADD('month', 1,

            DATETRUNC('month', [Date])

        )

    )

)

 

To get the first day of next month, we truncate to the first day of the moth, then simply add one month.

 

Month - Next - First Day

DATE(

    DATEADD('month', 1,

        DATETRUNC('month', [Date])

    )

)

 

For the last day of next month, we first truncate to the first day of the month. Then we add two months, giving us the first day of the month after next. We then subtract a day, giving us the last day of next month.

 

Month - Next - Last Day

DATE(

    DATEADD('day', -1,

        DATEADD('month', 2,

            DATETRUNC('month', [Date])

        )

    )

)

 

For the first day of the previous month, we first truncate to the first day of the month, then subtract one month.

Month - Previous - First Day

DATE(

    DATEADD('month', -1,

        DATETRUNC('month', [Date])

    )

)

 

And, to get the last day of the previous month, we truncate the to the first day of the month, then simply subtract one day.

 

Month - Previous - Last Day

DATE(

    DATEADD('day', -1,

        DATETRUNC('month', [Date])

    )

)

 

The calculations for weeks, quarters, and years are all relatively similar to the above (with some slight difference), so I won’t show them all here.

 

Number of Days

Now let’s take a look at the number of days calculations. When dealing with months, I’ve observed people attempt to perform these calculations using case statements by month, but those have to account for leap year rules, which are more complicated than just every four years. We’ll avoid the need for special logic, building calculations that inherently handle leap years. Essentially, we’ll find the last day of the month (using the formulas above), then get the day of that date. While we could simply reference the calcs we’ve already created, I chose to make these calculations independent of each other so that you can copy just the ones you need.

 

Month - Current - Number of Days

DAY(

    DATEADD('day', -1,

        DATEADD('month', 1,

            DATETRUNC('month', [Date])

        )

    )

)

 

Month - Next - Number of Days

DAY(

    DATEADD('day', -1,

        DATEADD('month', 2,

            DATETRUNC('month', [Date])

        )

    )

)

 

Month - Previous - Number of Days

DAY(

    DATEADD('day', -1,

        DATETRUNC('month', [Date])

    )

)

 

The Quarter and Year calculated fields are a bit different than the Month calcs. For quarters, we calculate the number of days between the first and last day of the quarter (and add one day). For year, we get the day of year datepart of the last day of the year. The number of days in a week is always seven, so there are no Number of Days calculated fields for weeks.

 

Number of Weekdays

The number of weekdays in a period of time is a bit more difficult to calculate than the number of days as it needs to throw out weekends. Fortunately, Tableau has a great knowledge base article for this: Calculating Number of Weekdays Between Dates. I’ve leveraged this technique here. The logic is a bit too much to cram into independent calculated fields, so each of these has two helper calculations, which refer back to the first and last day calcs in the first section. So, if you use these calcs, you need to also copy the calcs on which these are dependent.

 

Let’s just look at the number of weekdays in the current month. The first helper calculation finds the first weekday in the month by shifting the first day forward as needed.

 

Month - Current - Fist Weekday

// Shift the date to the next weekday.

IF DATEPART('weekday', [Month - Current - First Day]) = 1 THEN

    // Sunday, so push 1 day forward to Monday

    DATEADD('day', 1, [Month - Current - First Day])

ELSEIF DATEPART('weekday', [Month - Current - First Day]) = 7 THEN

    // Saturday so push 2 days forward to Monday`

    DATEADD('day', 2, [Month - Current - First Day])

ELSE

    // Already a weekday.

    [Month - Current - First Day]

END

 

Similarly, the second calculated field finds the last weekday in the month by pushing the last day backward as needed.

 

Month - Current - Last Weekday

// Shift the date to the previous weekday.

IF DATEPART('weekday', [Month - Current - Last Day]) = 1 THEN

    // Sunday, so push 2 days back to Friday

    DATEADD('day', -2, [Month - Current - Last Day])

ELSEIF DATEPART('weekday', [Month - Current - Last Day]) = 7 THEN

    // Saturday so push 1 day back to Friday

    DATEADD('day', -1, [Month - Current - Last Day])

ELSE

    // Already a weekday.

    [Month - Current - Last Day]

END

 

With these, we can then calculate the number of weekdays using the formula in the knowledge base article.

 

Month - Current - Number of Weekdays

DATEDIFF('day', [Month - Current - Fist Weekday], [Month - Current - Last Weekday]) + 1

- 2 * DATEDIFF('week', [Month - Current - Fist Weekday], [Month - Current - Last Weekday])

 

The quarter and year calculations work in exactly the same way as the month calcs.

 

Miscellaneous

Finally, I’ve included calculated fields to get Decade, Century, Last of a Specific Day in a Month, and Nth of a Specific Day in a Month. Let's start with Decade and Century. Unfortunately, there is no DATETRUNC option for these, so we just need to do a little math to perform the truncation ourselves. For the decade, we really just want to drop the final number of the year, replacing it with a zero. We can do that by dividing the year by 10, truncating the value to its integer portion, then multiplying by 10. For instance, take the year 2015. If we divide by 10, we get 201.5. We then truncate to the integer portion, giving us 201. Finally, we multiply by 10 to get 2010. Century works in exactly the same way except we divide and multiply by 100. And, if you ever needed it, you could do the same thing for millennium using 1000.

 

Decade

// Truncate to the decade.

INT(YEAR([Date])/10)*10

 

Century

// Truncate to the century.

INT(YEAR([Date])/100)*100

 

Next, let’s talk about getting the last of a specific day in the month. For example, I might want to get the last Sunday of the month. This is a bit tricky, so to keep things as understandable as possible, we’ll create one helper calc that gets the weekday of the last day of the month (we’re using a previously-created calc for this).

 

Last Day - Helper Calc 1

// Weekday of the last day of the month.

DATEPART('weekday', [Month - Current - Last Day])

 

Rather than 7 separate calculated fields for each day of the week, I’ve created a parameter called Day of Week which will contain the numeric day of the week (Sunday = 1, Monday = 2, etc.). You can use this to specify which day of the week you need (or you can eliminate it and hard-code it into the calc). The calculated field will start with the last day of the month then step backward until it reaches the day you’ve specified.

 

Last Specific Day of Month

// We'll start with the last day of the month.

// Then we'll add days to get to the specified day of the week.

[Month - Current - Last Day]

-

IF [Last Day - Helper Calc 1] = [Day of Week] THEN

    // First day is the right weekday, so no need to add days.

    0

ELSEIF [Day of Week] > [Last Day - Helper Calc 1] THEN

    // The day we need is after the first day.

    7-[Day of Week]+[Last Day - Helper Calc 1]

ELSE

    // The day we need is before the first day.

    [Last Day - Helper Calc 1]-[Day of Week]

END

 

Finally, my most complex calculated field will find the nth of a specific day in the month. For example, you could use this to get the third Wednesday of the month. The logic of this calculated field is similar to the above, but has to account for the instance of the day you wish to return (1st, 2nd, 3rd, 4th, or 5th). In the same way as I used Day of Week to parameterize the Last Day calculated field, I’ve created another parameter, Day Instance which will specify the instance of that day you need (I’ll also be using Day of Week again). 

 

The logic of this calculated field is a bit more complicated, so we’re going to build 2 helper calcs. We’ll start by getting the weekday of the first day of the month.

 

Nth Day - Helper Calc 1

// Weekday of the first day of the month.

DATEPART('weekday', [Month - Current - First Day])

 

Next, we’ll create a calculated field with similar logic as the Last Day calc. However, there are two primary differences. First, we’re always starting with the first day of the month then counting forward. And, second, we’re accounting for the instance number in our math (essentially, adding multiples of 7 days, as needed).

 

Nth Day - Helper Calc 2

// We'll start with the first day of the month.

// Then we'll add days to get to the specified day of the week.

// Then we'll add extra chunks of 7 days to get to the specified week/instance.

[Month - Current - First Day]

+

IF [Nth Day - Helper Calc 1] = [Day of Week] THEN

    // First day is the right weekday, so no need to add days.

    0

ELSEIF [Day of Week] > [Nth Day - Helper Calc 1] THEN

    // The day we need is after the first day.

    [Day of Week]-[Nth Day - Helper Calc 1]

ELSE

    // The day we need is before the first day.

    7-[Nth Day - Helper Calc 1]+[Day of Week]

END

+

7*([Day Instance]-1)

 

But we’re not quite done yet. In some months, a given day of the week could have five instances, while others will only have four. Thus, we need to deal with a situation where you’ve chosen the fifth instance, but it doesn’t exist. To address this, our final calculated field will check to make sure that the day is in the same month as the first day of the month. If not, then it’s an invalid date and will return NULL.

 

          Nth Specific Day of Month

// If the 5th week was chosen, the nth day could be in the next month.

// If this is the case, make the value null.

IF DATETRUNC('month', [Nth Day - Helper Calc 2]) <> DATETRUNC('month', [Month - Current - First Day]) THEN

    NULL

ELSE

    [Nth Day - Helper Calc 2]

END

 

One final note on the Last and Nth day calculations. Both of these are only looking at the current month. However, if you need them to look at the previous or next month, you can simply swap out the calculated field referenced. For instance, if we wanted the previous month for our Nth day calc, we’d swap out [Month - Current - First Day] for [Month - Previous - First Day] in each of the three calculated fields.

The Workbook

I want to make it very easy for you to use these calculations when you need them, so I’ve created a workbook that contains all of them.

 

 

The workbook has a table listing all of the calculated fields available. You can use the filters to choose specifically what you’re looking for, then the table will show you the name of the calculated field as well as the folder they are in. You can then simply download the workbook, copy the calculated field (and any dependencies), then paste them into your workbook.

 

To make sure the calc is doing what you need, you can use the Example Date parameter. After entering a date, each row in the table will show the return value of the calculated field. For the Last Day calc, you’ll also need to enter a value in the Day of Week parameter. And, for the Nth Day calc, you’ll need to specify both Day of Week and Instance Number.

 

Note: All the calculated fields are well-commented so that you can easily follow what they are doing and why.

 

 

Thanks so much for reading! I hope you find this cheat sheet helpful and that it saves you some time when you’re working with dates. If you have any other favorite date calculations, feel free to share them in the comments.

 

Header image by Starline (Freepik.com)

 

Ken Flerlage, April 12, 2021

Hire Us! | Twitter | LinkedIn | GitHub | Tableau Public