QuickSight has truncDate for the start of a period but nothing for the end. The reliable way to get
the last day of a month is to go to the first day of the next month and subtract one day. It handles
28, 29, 30 and 31-day months, leap years included, without any rule of its own.
Copy and paste
Last day of the month of a date:
addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', {Order Date})))
Last day of the current month:
addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', now())))
Last day of the previous month:
addDateTime(-1, 'DD', truncDate('MM', {Order Date}))
Number of days in the month:
extract('DD', addDateTime(-1, 'DD', addDateTime(1, 'MM', truncDate('MM', {Order Date}))))
Is the date the last day of its month?
ifelse(extract('MM', addDateTime(1, 'DD', {Order Date})) <> extract('MM', {Order Date}), 'Yes', 'No')
How it works
Read the first formula from the inside out:
truncDate('MM', {Order Date})returns the first day of the month.addDateTime(1, 'MM', ...)moves to the first day of the next month.addDateTime(-1, 'DD', ...)steps back one day.
| Order Date | Month end | Days in month | Last day of its month? |
|---|---|---|---|
| 2026-02-10 | 2026-02-28 | 28 | No |
| 2024-02-10 | 2024-02-29 | 29 | No |
| 2026-04-30 | 2026-04-30 | 30 | Yes |
| 2026-12-15 | 2026-12-31 | 31 | No |
The last formula asks a simpler question: does tomorrow fall in another month? It needs no month end at all.
Why not add a month to the date itself
addDateTime(1, 'MM', {Order Date}) followed by “minus the day number” looks shorter, but it adds a
month to dates like 31 January, and the AWS documentation does not say what QuickSight returns when the
target month has fewer days. Moving between first days of the month never meets that case.
Month end in a filter or a range
When {Order Date} holds times as well as dates, the last day of the month starts at midnight, so
{Order Date} <= {Month end} drops everything after 00:00 on that day. For ranges, compare with the
first day of the next month instead. The current month, for example:
{Order Date} >= truncDate('MM', now())
AND {Order Date} < addDateTime(1, 'MM', truncDate('MM', now()))
Keep the month end for labels, due dates and “days left in the month” calculations.
Related guides
- How to calculate the first day of a month
- How to handle 29 February in a prior year comparison
- The same calculation in Tableau
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
