The first day of the month is the usual key for monthly reporting: every date in September 2026
becomes 1 September 2026, and the year stays attached, so September 2025 and September 2026 remain
separate. In QuickSight it is one function, truncDate.
Copy and paste
First day of the month of a date:
truncDate('MM', {Order Date})
First day of the next month:
addDateTime(1, 'MM', truncDate('MM', {Order Date}))
First day of the previous month:
addDateTime(-1, 'MM', truncDate('MM', {Order Date}))
First day of the current month:
truncDate('MM', now())
What truncDate returns
truncDate(period, date) keeps the date down to the period you name and sets everything below it to
the start: the day becomes 1 and the time becomes midnight.
| Order Date | Month start | Next month | Previous month |
|---|---|---|---|
| 2026-09-15 | 2026-09-01 | 2026-10-01 | 2026-08-01 |
| 2026-09-30 | 2026-09-01 | 2026-10-01 | 2026-08-01 |
| 2026-10-01 | 2026-10-01 | 2026-11-01 | 2026-09-01 |
| 2026-01-31 | 2026-01-01 | 2026-02-01 | 2025-12-01 |
The result is a date, not text, so it sorts in calendar order, works in date filters and can be passed to the other date functions.
Truncate first, then add months
The next and previous month formulas truncate before they add. The order matters. addDateTime
moves a date by whole months, and the AWS documentation does not say what it returns when the target
month is shorter, for example one month after 31 January. A first day of the month always exists in
the target month, so truncating first gives the same answer on every engine and every date.
Calculation or field well
If you only want a chart by month, you don’t need a calculated field: put {Order Date} in the field
well and set its aggregation to Month. Use the calculated field when the month has to be a value:
- to compare two dates, for example orders placed in the month a customer signed up;
- in a filter or another calculation, for example
{Order Date} >= truncDate('MM', now())for the current month; - as a stable key in a dataset shared by several analyses.
Where to create it
A calculated field added to the dataset is computed when SPICE is refreshed and is available in every
analysis. A field added in an analysis exists only there. For truncDate('MM', {Order Date}) either
works.
Formulas that call now() belong in the analysis. In a SPICE dataset, row-level calculated fields are
stored at refresh time, so “the current month” would stay frozen at the last refresh.
Related guides
- How to calculate the last day of a month
- How to calculate the first day of the week, quarter or year
- The same calculation in Tableau
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
