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
The approach I recommend is essentially the same as for the specific date example, but using the Time Frame filter settings for dates. A count of active memberships today would be a count of memberships with a start date on or before today and an expiration date on or after today.
For the Start before Today filter, I started with Time Frame = Last 360 Days (which includes today)...
...but that could miss memberships that started 364 days ago and expire tomorrow. So, from here I go to Advanced and alter the count of days from 360 to 99999 (offset 0 means today is included in the count).
==>
Repeating that process for Expr after Today, setting a Time Frame = Next 360 Days. This defaults to excluding today (offset of 1), which we don't want. So, flipping to Advanced, I set the count to 99999 again, and the offset from 1 to 0.
With the Today value set up, we can duplicate the Value and update it to count memberships active Today Last Year, editing the Advanced filters as follows:
Again, duplicate the Value and update the offsets setting them each an additional 365-days offset.
5YearActiveonDate.dash
One may wonder about the validity of using 365-day increments which ignore leap years and might be reporting on yesterday in some prior year rather than today. To have such date-alignment year over year would look really weird when today is 29-Feb where the prior 3 years wouldn't have any active memberships on that date. So, I recommend this relative 365 days over a more explicit date-yoy, but if explicit dates are really the need, then we would need to talk customization to enable that effectively.
Thanks,Chris