How to Calculate MTD Sales for the Last 3 Months

You need a saved search that returns Month-to-Date sales totals for the current month plus each of the prior two months, with each month as its own column. A common attempt uses a BETWEEN clause with date offsets, but the offset approach drifts at month boundaries. The trick is treating each month as its own date window anchored to the first of the month, not as a number of days back from today.

The Scenario: Three MTD Columns, One Saved Search

A sales director wants a single report showing MTD totals for October, September, and August as of today (let's say October 15). The current MTD is straightforward because trandate falls between the first of this month and today. The previous two months require their own windows, not just ADD_MONTHS({today}, -60).

The formula the original poster had working for last MTD almost worked, but a day-offset pattern like BETWEEN ADD_MONTHS({today}, -60) AND ADD_MONTHS({today}, -31) is fragile. February has 28 days, March has 31, and any hard-coded day count will either include March 1 sales in your February column or exclude February 28. NetSuite treats invoices, bills, orders, and cash sales as transactions, all searchable through the Transaction search type, which is what you build this report on.

The Three Formulas You Need

Create three Formula (Numeric) columns. Each uses CASE WHEN with a date window that anchors to a specific month, not a relative offset from today.

Current MTD:

CASE WHEN {trandate} BETWEEN TRUNC({today},'MM') AND {today} THEN {amount} ELSE 0 END

Last Month MTD (Month -1):

CASE WHEN {trandate} BETWEEN ADD_MONTHS(TRUNC({today},'MM'), -1) AND ADD_MONTHS({today}, -1) THEN {amount} ELSE 0 END

Two Months Ago MTD (Month -2):

CASE WHEN {trandate} BETWEEN ADD_MONTHS(TRUNC({today},'MM'), -2) AND ADD_MONTHS(ADD_MONTHS(TRUNC({today},'MM'), -1), -1) THEN {amount} ELSE 0 END

The start of each window is ADD_MONTHS(TRUNC({today},'MM'), -N), and the end is the day before the next window starts. For Month -2, the end date is the day before Month -1 begins, which is ADD_MONTHS(ADD_MONTHS(TRUNC({today},'MM'), -1), -1). NetSuite's SQL Expressions documentation confirms that these formula functions are evaluated by the Oracle database, so TRUNC, ADD_MONTHS, and CASE all behave according to standard SQL.

Why "2 Months Prior" Breaks the Simple Pattern

A natural instinct is to write BETWEEN ADD_MONTHS({today}, -60) AND ADD_MONTHS({today}, -31) for "two months ago." That works on paper, but it hard-codes day counts that drift with month length. If your formula assumes 30 days back, you'll either include March 1 sales in your February column or exclude February 28.

Using TRUNC({today},'MM') as the anchor sidesteps this entirely. The MM format mask in TRUNC returns the first day of the month, and ADD_MONTHS moves whole months without counting days. The end boundary for Month -2 is always "the day before Month -1 starts," regardless of whether those months have 28, 30, or 31 days.

The trap with "X months ago" date math in saved searches is that ADD_MONTHS moves by whole months, but today - 60 moves by days. Mixing the two produces off-by-one errors at month boundaries.

Building the Search Step by Step

1. Go to Reports > New Saved Search and select Transaction as the record type.
2. On the Criteria tab, add a filter: Type is any of Sales Order, Invoice, or Cash Sale, depending on what you count as "sales."
3. Add another filter: Date is within the last 3 months. This is a coarse filter for performance, since the formulas do the precise bucketing.
4. On the Results tab, add Date with a Summary Type of Month and a Summary Type of Group. This gives you one row per month.
5. Add three Formula (Numeric) columns with the expressions above. Set the Summary Type for each to Sum.
6. Save and run. You should see three columns (Current MTD, Last MTD, 2-Months-Ago MTD) aligned with their month group.

If your fiscal calendar doesn't align with calendar months, replace TRUNC({today},'MM') with TRUNC({today},'FM') and adjust the format mask to match NetSuite's fiscal calendar options. Fiscal month handling is where most "why is my November column wrong" questions come from.

Edge Cases to Check Before You Publish the Report

Partial month on the current column. If today is October 15, the Current MTD column shows 15 days of data, while the other two columns show full months. Anyone reading the report will see a smaller number and assume a sales slump. Add a column header label like "MTD (15 days)" or display "as of {today}" in the report title.

Leap years. February 29 only matters if your "two months ago" window crosses a February in a leap year. The ADD_MONTHS approach handles this correctly; a day-offset formula might not. If you're writing the formula in February 2028 and the window spans a leap February, double-check that the end boundary for Month -1 resolves to February 29 and not March 1.

Multi-currency subsidiaries. If you're running this across subsidiaries with different base currencies, {amount} returns the transaction currency, not the base. Filter to a single subsidiary for clean numbers or add a Formula (Currency) column that converts at the consolidated rate. Consolidated reporting often requires a different aggregation approach than the simple SUM({amount}) shown here.

When You Actually Need SuiteQL Instead

The three-column approach above works for straightforward cases. If you need to pivot dynamically (show the last 6 months, or let users pick the range), the saved search formula approach becomes unwieldy because each column is a hard-coded expression. A SuiteQL query against the Transaction table gives you one row per month with a clean GROUP BY:

SELECT
  TO_CHAR({trandate}, 'YYYY-MM') AS month_key,
  SUM(CASE WHEN {trandate} <= {today} THEN {amount} ELSE 0 END) AS mtd_amount
FROM transaction
WHERE {trandate} BETWEEN ADD_MONTHS(TRUNC({today},'MM'), -2) AND {today}
GROUP BY TO_CHAR({trandate}, 'YYYY-MM')
ORDER BY month_key DESC

Run this from a RESTlet or a custom portlet when the saved search column count exceeds five or six. The pivot logic moves out of the formula and into the query, which scales better and avoids the syntax brittleness of nested TRUNC and ADD_MONTHS calls.

Once the three columns render correctly, test the report on the first day of a new month. The "Current MTD" should reset to show only one day of data, and what was "Last MTD" should shift to a full prior month. If it doesn't, the most common culprit is a cached saved search definition; go to the search and click Refresh or re-save to force a re-parse of the formulas.