On 15 May, May’s sales cover half a month. Compared with the whole of April they always look like a drop.
The fair benchmark is the same days of April: 1 to 15 May against 1 to 15 April. A monthly bar chart with
periodOverPeriodPercentDifference compares complete months, so this comparison needs its own measures.
Copy and paste
AsOfDate is a Datetime parameter (use truncDate('DD', now()) for a live dashboard).
Last day of the previous month (save as {Prev Month End}):
addDateTime(-1, 'DD', truncDate('MM', ${AsOfDate}))
The same day last month, capped at month end (save as {AsOf LM}):
ifelse(
extract('MM', addDateTime(1, 'DD', ${AsOfDate})) <> extract('MM', ${AsOfDate})
OR extract('DD', ${AsOfDate}) >= extract('DD', {Prev Month End}),
{Prev Month End},
addDateTime(-1, 'MM', ${AsOfDate})
)
Month to date and the same days of last month:
sumIf({Sales}, {Order Date} >= truncDate('MM', ${AsOfDate})
AND {Order Date} < addDateTime(1, 'DD', ${AsOfDate}))
sumIf({Sales}, {Order Date} >= truncDate('MM', {Prev Month End})
AND {Order Date} < addDateTime(1, 'DD', {AsOf LM}))
Example
The same orders as in the Snowflake version of this guide:
| Order Date | Sales |
|---|---|
| 2026-04-03 | 60 |
| 2026-04-08 | 80 |
| 2026-04-15 | 40 |
| 2026-04-16 | 90 |
| 2026-04-28 | 200 |
| 2026-05-02 | 120 |
| 2026-05-15 | 50 |
| 2026-05-20 | 300 |
As of 15 May 2026:
| Period | From | To | Sales |
|---|---|---|---|
| This month | 2026-05-01 | 2026-05-15 | 170 |
| Last month, same days | 2026-04-01 | 2026-04-15 | 180 |
| Last month, complete | 2026-04-01 | 2026-04-30 | 470 |
Over the same fifteen days, May is 5.6% behind April. Against the whole of April it would look 63.8% behind.
When the days do not line up
Months have 28 to 31 days. {AsOf LM} handles the two cases where the same day number does not work:
| AsOfDate | AsOf LM | Why |
|---|---|---|
| 2026-03-28 | 2026-02-28 | February ends on the 28th |
| 2026-03-30 | 2026-02-28 | there is no 30 February |
| 2026-03-31 | 2026-02-28 | month end |
| 2026-04-30 | 2026-03-31 | month end: the whole of March, not 30 March |
| 2026-05-15 | 2026-04-15 |
- The day does not exist last month (30 March): the benchmark stops at the end of February. The
formula caps it itself, so it does not depend on what
addDateTimedoes with a missing day. - The reference date is a month end (30 April): the month is complete, so the benchmark is the complete previous month. A plain “one month earlier” would compare all of April with March minus its last day.
From 28 March onwards, March keeps growing to 29, 30 and 31 days while the benchmark stays at February’s 28. That cannot be avoided; when the lengths differ, compare sales per day as well.
Sales per day
{MTD} / (dateDiff(truncDate('MM', ${AsOfDate}), ${AsOfDate}) + 1)
The denominator is the number of days covered, both ends included: 15 for 1 to 15 May. Use the same
pattern with {Prev Month End} and {AsOf LM} for last month.
Checked against the Amazon Quick Sight function reference (October 2026). The example results were recomputed in DuckDB.
