Chart of Active Customers

[spancakes]

I am trying to figure out a way to generate a bar chart of our active customers.

Each of our customer records have a "start date" and an "end date" when they were active. I can easily graph the "start dates" (see attachment) but this doesn't show our cumulative numbers including people who are still active customers. If someone stops being a customer, I still want them to be counted for the years they were active, but not from their cutoff date.

The closest I have come to doing this is by linking to another table that has a list of years and basically manually adding customers to years they were active, but surely there is a better way to do this. We have hundreds and hundreds of people. Maybe there's a formula that can do something clever, dunno.

Ideas?

[TheTimeSavingCo]

Hmm, you could technically make a formula to output all the years within a date range I think, you'd just need to check whether each individual year fell within that range, and if it did, output it as text.  This would give you a formula field that contained all the years a customer was active, and you could then copy this field's value and paste it into the linked field to that 'Years" table you mentioned, which would populate the graph like you want