How to find the end-of-month date in Tableau

When performing date calculations in Tableau, there are often scenarios where you want to calculate the last day of the month.
For example:

• You want to align the closing dates for monthly reports

• You want to unify date granularity across multiple data sources

• You want to visualize KPIs as of the end of the month

These are common processes in practical work.

In this article, I will explain how to find the end-of-month date in Tableau, along with the actual calculation formulas used.

■ Method 1: Combining DATETRUNC and DATEADD

✔ Calculation Formula

DATEADD('day', -1, DATEADD('month', 1, DATETRUNC('month', [Date])))

✔ Explanation

1. DATETRUNC('month', [Date])
→ Find the first day of the current month
2. DATEADD('month', 1, ...)
→ Find the first day of the next month
3. DATEADD('day', -1, ...)
→ Subtract one day from the first day of the next month

✔ Features

• This is the most commonly used method in Tableau

• Works stably in all versions

• Can be applied to any date column

■ Method 2: Using MAKEDATE and YEAR / MONTH

✔ Calculation Formula

DATEADD(
'day',
-1,
MAKEDATE(
YEAR([Date]),
MONTH([Date]) + 1,
1
)
)

✔ Explanation

1. MAKEDATE(YEAR([Date]),MONTH([Date]) + 1,1)
→ Creates the 1st of the next month
2. DATEADD('day', -1, …)
→ Creates the day before the 1st of the next month

✔ Features

• Easy to understand because it explicitly constructs the 'year, month, and day'

• Safe for December as well, because Tableau handles the carry-over even if the month becomes 13

• Visually easy to explain even to beginners

■ Recommended for these situations

Method 1

• When you want to use it for related period calculations such as start of the month, end of the previous month, or start of the next month in addition to the end of the month

• When used in conjunction with LOD / Table Calculations / Date Granularity Normalization (e.g., creating axes for monthly aggregation)

• When the main goal is to 'first round to the month' for data containing DateTime

Method 2

• When you definitely want to create an 'End-of-month date (Date)' (time is not needed)

• When you want the 'date itself' for accounting or closing date logic

• When you want to make the 'end-of-month calculation' understandable at a glance from the formula (for handovers/explanations)

■ Summary

There are several ways to write formulas in Tableau just to find the end-of-month date, but
once you grasp the concept of '1st of next month - 1 day', your application possibilities will expand significantly.

End-of-month, beginning-of-month, and monthly aggregation are themes that inevitably appear in any business.
I hope these calculation formulas serve as a hint for your daily analysis and dashboard creation.