Gross revenue retention (GRR): formula and example.

WITH per_customer AS (
  SELECT customer_id,
         SUM(mrr) FILTER (WHERE started_at <= current_date - interval '1 year'
           AND (ended_at IS NULL OR ended_at > current_date - interval '1 year')) AS start_mrr,
         SUM(mrr) FILTER (WHERE started_at <= current_date
           AND (ended_at IS NULL OR ended_at > current_date)) AS end_mrr
  FROM subscriptions
  GROUP BY customer_id
)
SELECT SUM(start_mrr) AS start_mrr,
       SUM(LEAST(COALESCE(end_mrr, 0), start_mrr)) AS retained_mrr,
       ROUND(100.0 * SUM(LEAST(COALESCE(end_mrr, 0), start_mrr)) / NULLIF(SUM(start_mrr), 0), 1) AS grr_pct
FROM per_customer
WHERE start_mrr > 0;

GRR (12 months) % =
VAR StartDate = EDATE(MAX('Date'[Date]), -12)
VAR Window = DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH)
VAR Cohort =
    CALCULATETABLE(VALUES(MrrMovements[customer_id]), REMOVEFILTERS('Date'), MrrMovements[month] <= StartDate)
VAR StartMRR = CALCULATE([MRR], DATEADD('Date'[Date], -12, MONTH))
VAR Lost =
    CALCULATE(SUM(MrrMovements[amount]), Window, Cohort, MrrMovements[movement_type] IN {"contraction", "churn"})
RETURN
    DIVIDE(StartMRR - Lost, StartMRR)