Add compare-to (flux analysis) filter for period-over-period comparison in Insights

[MelvinBot]

Summary

Add a compare-to filter to Insights reports that enables period-over-period comparison (flux analysis). When a group-by and date range are present, users can compare the current period's data against a previous or average period — supporting use cases like month-over-month spend tracking, year-over-year comparison, and cumulative budget monitoring.

Slack thread: https://expensify.slack.com/archives/C090U774ZH7/p1776062694128159

Background

Expensify already has powerful native flux analysis capabilities, but they are only accessible via VirtualCFO. This feature exposes that power directly to users through the Insights UI.

Requirements

1. Add compare-to filter (when group-by and date range are present)

The compare-to filter compares the current selected date range to a same-sized date range offset by the specified period. Supported values:

• [N][day|week|month|year][s] — Offsets the date range by the number of periods specified (natively supported by SQLite). Examples:
  • month — Shifts the date range back to start on the same day of the month, 1 month prior
  • 2weeks — Shifts the date range back 14 days
  • year — Same date range, one year prior
• average[N] — Calculates the average of the N previous ranges leading up to the current date range. If N is unspecified, default to the minimum number of periods to cover a year of data.

2. Add UI controls for compare-to

• When a date range is selected, show a "Compare to" selector with contextual labels:
  • If the date range corresponds to a clean week/month/quarter/year, use that label (e.g., "Previous month", "This month last year", "The average month")
  • If the date range is an arbitrary custom range, use generic labels (e.g., "Previous period", "This period last year", "The average period")
• The UI should validate that compare-to options are consistent with the selected date range

3. Update table output to show comparison results

• Add columns for the comparison period values alongside the current period
• Include percentage change (delta) columns

4. Update chart output to show comparison results

• Overlay or display comparison period data alongside the current period on charts
• Support cumulative view: track spending in the current period (e.g., by day within the month) against the comparison period's cumulative trend, enabling proactive budgeting — users can see how current spending tracks against historical patterns before the period ends

Design considerations

• Periods being compared may be of different lengths (e.g., comparing progress mid-month against a full previous month) — the system should handle this gracefully, especially for cumulative views
• The cumulative comparison is particularly valuable: it lets users monitor in-progress spending against historical baselines, turning analysis into proactive budgeting