Hello all! Taking a leap with what might be a very elementary question and hoping you can help!
I want to create a widget that will give me a 5-year comparative report on my total membership numbers on a given date. For example, I have tried to create a formula from the Active Member Count that will take the current date and pull the membership totals on that date one year ago, two years, and so on. No success!
Do you all have a formulas, widgets, or tips on how to accomplish this?
Hi Kayla,
The formula for counting memberships that were active at a given moment in time requires filtering to memberships that had a start date prior to the end of the reporting period, and an expiration date after the start of the reporting period. For example, to get a count of active memberships in October 2022, we'd filter on Membership Start Date being on or before 2022-10-31 and Membership Expiration Date being on or after 2022-10-01. So, straightforward, but not elementary.
For a single widget to show an active member count from a given date, and then that date in the four prior years, I recommend something like this (I used October 1 as the given date):
PS. Here's my sample: 5YearActiveonDate.dash
And instructions for Importing a Dashboard
Thank you so much, Chris! This is incredibly helpful.
I would also love to know if there is a way to make the dates relative, so that the data is always current for when it is checked.
Yes, but can you be more specific for me? Are you thinking TODAY YOY, or some other related date or period?
Yes, "Today" would be ideal
This is amazing! Thanks Chris!