Billing Rules Comparison: All Dashboard Views

All four views query the same BQ table: DYNAMICS_DATA.Accounts_and_related_data (nested CRM snapshot). This document compares how each view handles the same billing concepts.

1. Base Filters (What rows get included)

Order-Level Cancellation

INCONSISTENCY: AM Portfolio PY uses {PY}-01-01 as cancellation cutoff, Revenue Bridge uses {PY}-12-01. These should probably be the same. Solution PY drops the filter entirely, which is the most permissive.

Order Line Cancellation

Same pattern: Bridge is softer on line cancellation to track churn timing. Solution PY is most permissive.

Signed Date Minimum

CONSISTENT across all views. Excludes legacy pre-migration orders.

Product Exclusions

CONSISTENT. DIR - PRINT always excluded, null products excluded, subscription flag respected.

2. Recurring Invoice (RI) Filters

Deposit Exclusion

CONSISTENT. Deposit RIs never count toward revenue.

RI Amount and Period

CONSISTENT. Zero-amount RIs excluded from revenue (except bundled PMS tracking in AM Portfolio).

RI Date Window (Active/Expired)

This is where the biggest differences are.

KEY DIFFERENCES:

• AM Portfolio best_ri does NOT check firstInvoiceOn for CY (relies on ROW_NUMBER to pick best RI). This means future-dated RIs that have not started billing yet WILL be included.
• Revenue Bridge uses the widest window (firstInvoiceOn < next year start, expireOn > prior Dec) then does monthly filtering in SQL with CROSS JOIN months.
• Solution Portfolio CY uses nextInvoiceOn >= NOW() which is stricter than AM Portfolio - it requires the RI to have a future invoice scheduled.
• AM Portfolio active_lines adds firstInvoiceOn <= NOW() which best_ri does NOT have.

3. Best RI Selection (ROW_NUMBER Dedup)

Each billing group can have multiple RIs. All views pick "the best one":

KEY DIFFERENCE: Revenue Bridge partitions by (bgId, salesOrderDetailId) - meaning it picks one RI per BG per order line. AM Portfolio and Solution partition by bgId alone - one RI per BG regardless of how many lines share that BG.

Impact: If a BG has 3 order lines, Revenue Bridge gets 3 rows (one per line), AM Portfolio gets 1 row (for the whole BG). Revenue Bridge then distributes the RI amount across lines via proportional split, so the math should converge, but the intermediate structures differ.

4. MRR Calculation

INCONSISTENCY: AM Portfolio LIST mode uses simple riAmount / riPeriod per BG. This is fine for AM-level totals (one BG = one value), but the PORTFOLIO mode uses the proportional split to show per-account product breakdowns. The two modes may not agree at the margins due to rounding.

5. Suspended vs Active Distinction

INCONSISTENCY: Revenue Bridge does NOT exclude suspended accounts from "active" MRR per month. If a BG is suspended but its RI window covers the month, it appears as active revenue. The bridge JS may handle this, but the SQL does not.

AM Portfolio separates suspended MRR from active MRR cleanly at the SQL level.

6. Setup/DEA Revenue

Setup Identification

CONSISTENT across all views.

Setup/DEA Year Window

INCONSISTENCY: Revenue Bridge uses a simpler filter (just firstInvoiceOn in year). AM Portfolio and Solution have the full 3-way OR that handles edge cases (lines with service periods but no RI, lines with RI but no dates).

Impact: Revenue Bridge may miss setup/DEA lines that have fromDate/toDate spanning the year but whose RI firstInvoiceOn is in a different year.

Setup/DEA Proration

INCONSISTENCY: A 24-month setup fee of E2,400 spanning 2025-2026 would show as:

• AM Portfolio: E1,200 (12/24 months in CY)
• Revenue Bridge: E2,400 (full amount in the firstInvoiceOn month)
• Solution Portfolio: E1,200 (prorated)

7. Prior Year Comparison

INCONSISTENCY: AM Portfolio compares to same-day-last-year, Revenue Bridge compares to Dec 31 prior year. For an AM looking at March 23 2026:

• AM Portfolio PY MRR = what was active on March 23 2025
• Revenue Bridge Opening = what was active in Dec 2025

8. BG Replacement Proxy

CONSISTENT (except Company View which does not need PY comparison).

9. Summary of Inconsistencies to Fix

10. Shared Rules (Canonical - should be the same everywhere)

These are the "golden rules" that should be identical across all views:

1. Exclude DIR - PRINT product group
2. Exclude null products
3. Exclude orders signed before 2017-09-04
4. Exclude deposit RIs (ri.isDeposit = TRUE)
5. Exclude zero-amount RIs (ri.totalLineItemAmount = 0)
6. Exclude zero-period RIs (ri.invoicePeriod = 0)
7. MRR = riAmount / riPeriod (per BG), allocated by baseAmount proportion across lines
8. Setup = product.setup flag OR name pattern matching
9. DEA = non-subscription, non-setup one-time fees
10. BG Replacement: use bg_replacement_proxy for PY comparison

11. Where Each View Lives

FCR Dashboard documentation · generated from docs/ · keep counts verified, not guessed.