truncDate works for every period, not only months. Quarters and years are straightforward. Weeks
need a decision, because QuickSight starts them on Sunday unless the analysis says otherwise.
Copy and paste
First day of the quarter and of the year:
truncDate('Q', {Order Date})
truncDate('YYYY', {Order Date})
First day of the week (Sunday, or the analysis week start):
truncDate('WK', {Order Date})
Monday of the week, whatever the settings:
addDateTime(-1 * mod(extract('WD', {Order Date}) + 5, 7), 'DD', truncDate('DD', {Order Date}))
Last day of the quarter:
addDateTime(-1, 'DD', addDateTime(1, 'Q', truncDate('Q', {Order Date})))
Example
| Order Date | Weekday | WK (Sunday start) | Monday of the week | Quarter start | Year start |
|---|---|---|---|---|---|
| 2026-05-10 | Sunday | 2026-05-10 | 2026-05-04 | 2026-04-01 | 2026-01-01 |
| 2026-05-11 | Monday | 2026-05-10 | 2026-05-11 | 2026-04-01 | 2026-01-01 |
| 2026-05-14 | Thursday | 2026-05-10 | 2026-05-11 | 2026-04-01 | 2026-01-01 |
| 2026-05-16 | Saturday | 2026-05-10 | 2026-05-11 | 2026-04-01 | 2026-01-01 |
The first row is the one to watch: with a Sunday start, Sunday opens a new week; with a Monday start, it closes the previous one.
The week start
The AWS documentation says that the week starts on Sunday, and that an author can change it for a whole analysis under Edit → Analysis settings → Date and time → Custom start day. The setting applies to week-level fields, filters and calculated fields of that analysis, and every dashboard published from it uses the same start.
Two consequences:
- The same formula can give different weeks in two analyses. A field defined in the dataset is shared by both, so AWS recommends defining week calculations at the analysis level when you change the start day.
- If your weeks must start on Monday everywhere, for example to match ISO weeks in the warehouse, use the Monday formula. It reads the weekday and does not depend on the setting.
How the Monday formula works
extract('WD', date) numbers the days from 1 (Sunday) to 7 (Saturday). Adding 5 and taking the
remainder of the division by 7 gives the number of days since Monday:
| Day | WD | mod(WD + 5, 7) |
|---|---|---|
| Monday | 2 | 0 |
| Tuesday | 3 | 1 |
| Saturday | 7 | 5 |
| Sunday | 1 | 6 |
addDateTime then goes back that many days from the start of the day. truncDate('DD', ...) removes
the time, so the result is midnight on Monday even for a timestamp. mod expects positive numbers,
which is why the formula adds 5 instead of subtracting 2.
Fiscal quarters and years
truncDate('Q') and truncDate('YYYY') follow the calendar. For a fiscal year that starts in April or
July, see how to calculate fiscal year to date.
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
