How to prepare historical reports on membership subscriptions

To report on membership numbers for a past date (e.g., for an annual report), you will need to calculate them by working backwards from your current data. This article explains the formula and process.

The Core Principle

The calculation relies on adjusting your current member count for joiners and leavers who have changed since your target date. The formula is:

Members on [Your Target Date] = Current Active Members - New Members (joined after date) + Lapsed Members (left after date)

How to Calculate the Total

Export Data

Navigate to Reports > Membership subscriptions and export all data to a spreadsheet.

Apply the Formula

In your spreadsheet, find the count for each part of the formula.

1. Find Current Active Members: Filter for subscriptions with status = Valid or Overdue. This is your starting number.
2. Subtract New Members: Filter for subscriptions with a Created Date after [Your Target Date]. Subtract this count from your total.
3. Add Back Lapsed Members: Find members with a Status of Cancelled or Expired who were active on your target date.
   • For Cancelled subscriptions, add back those with a Renewal Date after [Your Target Date]
   • For Expired subscriptions, add back those with a Renewal Date after [Your Target Date + 1 Year] . This is because the renewal date on expired records reflects the end of an unpaid period.

A small caveat: there may be occasional exceptions if manual changes were made to a specific record, but this method should give you a very accurate figure.

Getting a Breakdown by Membership Category

To get a breakdown by plan type, you must perform the entire calculation for each category separately.

Before you begin, filter your spreadsheet by the desired Membership plan (e.g. Student), then apply the full 1-2-3 calculation above. Clear the filter and repeat for each category required for your report.