The answer starts with the metric and date role

“Last week’s sales” does not have one universal value. Operations may mean paid orders including tax, finance may mean recognized revenue without tax, and fulfillment may mean shipped value. The query should resolve the approved metric in the current business domain, show the chosen date role and week rule, and ask a clarification question if multiple interpretations remain equally plausible. Until that choice is confirmed, do not return a figure selected only because the phrasing was historically common.

A useful answer exposes the interval, timezone, metric version, data cutoff, and refund treatment. Hiding those decisions in SQL or a system prompt prevents the user from telling an operational change from a change in accounting definition or refresh cadence. Microsoft’s star schema guidance connects measures to a stated fact grain and dimensional relationships; a date column is not an interchangeable label. The explanation and the figure should come from the same query plan, so the page wording cannot drift from the filters that actually ran.

Separate four kinds of time

Event time is when the business action happened, such as payment success. Processing time is when its record entered the warehouse. Effective time is when a rule or ownership assignment applied. Reporting time is the period the user requested. An order paid late Sunday but ingested Monday belongs to last week under a payment-date definition, even though a warehouse-ingest query puts it in the new week. One order can carry all four clocks; the query must name which one it used and must not blend them and then average.

Name date roles independently: created_at, paid_at, shipped_at, and booked_at answer different questions. The presence of several dates in one fact table does not authorize the model to select the first column it finds. Each governed metric should state its permitted date roles, default role, and conditions under which clarification is mandatory. If the metric declares no default role, stop and ask instead of taking the first date column in the table.

Resolve natural language into an explicit interval

A common rule maps “last week” to the preceding complete business week in the business timezone, using a half-open interval from the week start to the next week start. The organization must decide whether weeks start on Sunday or Monday. A store that trades past midnight may assign early-morning sales to the previous trading day; truncating UTC timestamps would be wrong for that store. Example: in the Shanghai business timezone with Monday week starts, last week runs from Monday 00:00 up to the next Monday 00:00, and a Sunday evening payment still sits inside that window.

Months, quarters, and fiscal years also need a versioned calendar. A retail 4-4-5 calendar is not the same as a Gregorian month. “Same period last year” might mean matching dates, trading weeks, or the same number of open days. Leap days, holidays, and daylight-saving transitions require business rules rather than ad hoc model guesses. Apply calendar versions by effective date, so a week-start change this year does not silently restate last year under the new rule.

Fact grain controls aggregation

An order header has one row per order, an order-line fact one row per item, and a payment fact one row per transaction. Joining header revenue to lines and summing the result duplicates multi-item orders. Declare what one fact row means and whether a measure is additive before grouping or joining. Kimball’s grain principle provides a practical basis for this decision. Example: an order with three item lines still counts its header amount once, and the weekly total returns to order or customer grain first.

Daily summaries speed up recurring dashboards but cannot automatically answer an order-level refund investigation. Ratios such as average order value should be recalculated from the combined numerator and denominator, not averaged from daily percentages. Closing inventory and distinct customers require snapshot or deduplication rules across time. Averaging each weekly ratio and then averaging those ratios will drift from the control total a business owner already holds.

Expose the data watermark and restatement policy

“Week to date” should use a common complete watermark across the critical sales and refund sources. Combining sales refreshed this morning with refunds refreshed yesterday creates a misleading net figure. Each source needs its last complete partition, expected lag, and backfill policy. The response can say that data are complete through a particular local time and that later refunds may restate the period. Name the watermark timezone and use the same calendar as the metric date role, so “data has arrived today” is not read as complete event time.

A fast provisional morning estimate and a final finance number can coexist if clearly labeled. When late-arriving data exceed an agreed threshold, the system should defer a year-over-year claim or show a qualified range rather than silently comparing a partial period with a complete one. A label alone is not enough: an unqualified year comparison should not cite a provisional figure.

Compare implementation paths

Hand-built SQL templates work for a small set of fixed reports, but they proliferate when regions, date roles, and calendars diverge. A semantic layer centralizes metrics, time dimensions, calendars, and filters for reuse across tools. Natural-language analytics sits above that layer to parse requests and request clarification; it does not replace the definitions. Traditional BI leaves more choice to analysts and remains valuable for diagnosis, provided it uses the same semantic contract. When both chat and a fixed report exist, they must cite the same metric version, or the two figures cannot be reconciled.

Evaluate whether a candidate system displays its chosen date role, reuses an approved week rule, detects missing watermarks, and handles follow-up questions. A polished conversational interface cannot repair inconsistent underlying time semantics. Conversely, a governed metric provides a reliable baseline even if it initially appears only in a conventional report. Also check that a follow-up which changes the date role recomputes the window rather than only rewriting the sentence.

Implement against real questions and counterexamples

Collect ten actual questions using “yesterday,” “last week,” “this month,” and “same period” from sales, finance, and warehouse teams. For each, record owner, metric, date role, timezone, calendar, grain, filters, watermark, and expected clarification. Hand-calculate golden results for an order crossing midnight, a cross-week refund, a late-arriving record, and a multi-line order. The owner should keep the window endpoints and the expected row set with the golden total, not the total alone.

Have the semantic layer or AI-query system emit a structured query plan, then compare both its plan nodes and numbers against the golden cases. Attribute discrepancies to the interval, grain, filter, or data quality. Roll out one high-frequency metric first and retain the old report as a control until the business owner accepts both the result and its explanation. During the pilot, record the old and new plans side by side, show users only the confirmed result, and leave the gap for the owner to review.

Acceptance criteria and product boundary

Test local-midnight and week-start boundaries, consistent watermark handling, traceable restatements, conservation across weekly and monthly totals, and a follow-up that explicitly switches from payment to shipment date. A fluent answer that silently uses a UTC week, misaligned refunds, or a duplicated header amount should fail acceptance even if its arithmetic appears precise. A further counterexample is comparing an incomplete month-to-date window with last year’s complete month and stating an unqualified growth rate.

AskTable.ai may be assessed as an enterprise natural-language query entry point. This article does not establish that its current release implements every calendar, backfill, and clarification policy described here. Verify those capabilities with actual configuration, query traces, and the organization’s own data before relying on the result. Replay at least a midnight boundary, a cross-week refund, a late-arriving load, and a multi-line order.

Public references

Ready to help your team start?

Talk through a real scenario and see how AskTable.ai can fit your business.

Book a demo