What is the percentage change from zero?

The usual percentage-change formula is 100 × (current − previous) / previous. When the previous value is zero, that percentage is undefined. Report the absolute change and a clear zero-baseline label. A move from $0 to $80 is an $80 increase; it is not 0% growth or a finite 100% increase under this formula.

Define comparable periods before calculating

This synthetic example compares two complete periods of equal length for US, UK, German and French revenue. Each row is one market, and all amounts are already in USD under the same revenue definition. Revenue is nonnegative, current values are known, and a NULL previous value means the baseline is unavailable—not that no sales occurred. Currency conversion, refunds and late reporting are outside this fixture.

The US moves from $100 to $120. The UK moves from $0 to $80. Germany stays at $0. France has $60 now but no known previous value. These four cases need different explanations, even when several percentage cells are blank.

Run the SQL example

Tested with SQLite 3.50.4. Adapt numeric types and the inline fixture syntax to your database.

WITH revenue(market, previous_usd, current_usd) AS (
  VALUES ('US', 100, 120), ('UK', 0, 80),
         ('DE', 0, 0), ('FR', NULL, 60)
)
SELECT market, previous_usd, current_usd,
       current_usd - previous_usd AS change_usd,
       100.0 * (current_usd - previous_usd)
         / NULLIF(previous_usd, 0) AS pct_change,
       CASE WHEN previous_usd IS NULL THEN 'missing baseline'
            WHEN previous_usd = 0 THEN 'zero baseline'
            ELSE 'comparable' END AS comparison_status
FROM revenue ORDER BY market;

NULLIF(previous_usd, 0) produces NULL for a zero denominator. The separate status column preserves the reason the percentage is unavailable. Using 100.0 keeps this SQLite calculation from using integer division.

Read the results correctly

market previous current change_usd pct_change comparison_status
DE       0        0       0          NULL     zero baseline
FR       NULL     60      NULL       NULL     missing baseline
UK       0        80      80         NULL     zero baseline
US       100      120     20         20.0     comparable

For the US, report “up $20, or 20%.” For the UK, report “up $80 from a zero baseline; percentage change not applicable.” For Germany, the absolute value is unchanged at zero, but 0/0 still does not define the standard percentage. For France, neither the absolute difference nor the growth percentage can be established from the available baseline.

Why not wrap the result in COALESCE(…, 0)?

Replacing every missing percentage with zero collapses distinct cases into “no growth.” It would make the UK’s $80 increase look like a stable percentage, and it would conceal France’s missing history. Keep the numeric percentage nullable and use a display label such as “N/A—zero baseline” or “Unavailable—missing baseline.” Do not place those labels inside a numeric column that downstream calculations expect to aggregate.

Can you still calculate an overall growth rate?

Only for a clearly defined scope with complete, comparable baselines. Across the US, UK and Germany together, revenue goes from $100 to $200, so aggregate growth is 100%. That is a rate computed from summed amounts, not an average of market percentages. It includes the UK’s absolute increase even though the UK has no individual percentage.

France makes the full four-market comparison incomplete. Reporting $260 against the known $100 as if it were a complete baseline would conceal the missing French amount. You can present the three-market comparison with its scope stated, alongside current French revenue and the coverage limitation. Do not silently drop a market to improve the headline.

What should a production check cover?

Check a positive baseline, a zero-to-positive change, zero-to-zero, missing history and a positive-to-zero change. The last case is a valid −100% change. This query assumes known current amounts and nonnegative revenue; add explicit statuses for missing current values and a documented policy for negative baselines if your metric allows them. A refund-adjusted metric can require a different interpretation.

For the preceding reporting steps, see why LAG may not mean the previous month, NULL versus zero, and why averages of rates can mislead.

Source and verification

The official SQLite scalar-function reference documents NULLIF. Five executable checks verified the four market cases and the valid −100% decline; the three-market aggregate calculation is $100 to $200. These figures are synthetic practice data, not claims about actual market performance.