Test the dates on both sides of month end

Use amounts that reveal which dates were included

We added four synthetic documents to a fresh expense-workbook copy: September 30 for $1, October 1 for $2, October 31 for $4 and November 1 for $8. Each received its own identifier. The distinct amounts let the aggregate change identify the combination included, while row-level checks independently inspected every assigned period.

The starting October actual was $686,500 and year-to-date actual through October was $6,200,758. The expected changes were $6 for October and $7 for year-to-date. November’s $8 belonged to neither total. We wrote those expectations before refreshing the Actuals query.

This fixture takes US-formatted posting dates. Microsoft’s Date.FromText documentation supports explicit format and culture options. If your source uses another convention, test that convention directly; a successful US-format test does not validate day-first dates or timestamp-to-timezone conversion.

Inspect each assigned period and both totals

After the native query refresh and full calculation, the four records mapped to September, October, October and November respectively. The clean table contained 484 documents. October was $686,506; year-to-date was approximately $6,200,765.

Checking the detail and aggregate catches different failures. A correct total can conceal offsetting row mistakes. Correct individual dates can coexist with a report that filters the wrong period. This test requires both to agree.

The first failure was in our assertion

The first controller compared the year-to-date result with 6200765 using exact equality. Excel returned 6200765.0000000009, represented in the published JSON as 6200765.000000001. The period assignments and financial result were correct, but the assertion reported failure.

We replaced that comparison with an explicit absolute tolerance below half a cent and reran the experiment. It passed. Microsoft documents how binary floating-point representation can produce small arithmetic differences. This explains the observed residual; it does not authorize ignoring meaningful financial discrepancies.

Choose the comparison rule from the data contract. Here the input amounts are USD cents, and a $0.005 tolerance distinguishes a binary residual from a one-cent error. A model with fractional-cent rates or another currency needs its own precision and rounding policy. Do not round away a discrepancy before understanding it.

if ([math]::Abs($actualYtd - 6200765) -ge 0.005) {
    throw 'YTD does not reconcile within the fixture tolerance'
}

Adapt the boundary cases to the close policy

Download the saved boundary workbook, result including the prior assertion failure and script. Supply the synthetic expense workbook as expense-original.xlsx in a new directory.

The script creates its own copy and adds the four witnesses. It tests calendar-month date strings, not a fiscal calendar, business-day cutoff, timezone rule or late-posting policy. Add cases for those boundaries when they determine your reporting period.

Use Panes desktop execution to run native Excel. Keep this date test beside the duplicate-document guard and query freshness witness: correct dates do not establish unique or current inputs.