addDateTime(amount, period, date) moves a date forward with a positive amount and back with a negative
one. It is the only date arithmetic function in QuickSight, so due dates, “same day last year” and
rolling windows are all built on it.
Copy and paste
30 days after the order date:
addDateTime(30, 'DD', {Order Date})
One month earlier, one year earlier:
addDateTime(-1, 'MM', {Order Date})
addDateTime(-1, 'YYYY', {Order Date})
Seven days before today, at midnight:
addDateTime(-7, 'DD', truncDate('DD', now()))
An amount stored in a field:
addDateTime({Payment Terms Days}, 'DD', {Invoice Date})
Five working days after the order (Monday to Friday):
addWorkDays({Order Date}, 5)
Periods
The period is a quoted code: 'YYYY' years, 'Q' quarters, 'MM' months, 'WK' weeks, 'DD' days,
'HH' hours, 'MI' minutes, 'SS' seconds and 'MS' milliseconds. The same codes work in
truncDate, extract (which uses 'WD' for the weekday instead of 'WK') and dateDiff.
The function keeps the time of day: adding 4 days to 28 December 2016 at 06:45 returns 1 January 2017
at 06:45, as in the AWS example. Wrap the date in truncDate('DD', ...) when you want midnight.
The amount must be an integer
The documentation describes the amount as a positive or negative integer. A decimal field, or a
division such as {Hours} / 24, has to be converted first:
addDateTime(decimalToInt({Lead Time Days}), 'DD', {Order Date})
decimalToInt drops the decimals (29.99 becomes 29). For smaller steps, add hours or minutes instead
of fractions of a day.
Month ends
Adding months to the 29th, 30th or 31st of a month can land on a day that does not exist, for example
one month after 31 January. The AWS documentation does not say what addDateTime returns in that case.
Two ways to stay independent of it:
- Move between first days of the month, then step back: the last day of the month and the first day of next month both work that way.
- When you need “the same day one month or one year earlier”, test the month-end case yourself, as in how to handle 29 February and how to compare an incomplete month with last month.
If you rely on addDateTime with months anyway, check a few month-end dates in a table visual before
publishing.
Working days
addWorkDays(date, n) skips Saturdays and Sundays, and a negative n goes back. Like
netWorkDays, it knows no public holidays; see
how to count working days between two dates
for what to do about them.
Checked against the Amazon Quick Sight function reference (October 2026).
