Answers.Body: <p>Paul-Tahoe's answer is the correct one for your use case.  If you need point-in-time queries generally, consider putting Civi data in a data warehouse that supports OLAP cubes.  Parvez Saleh at <a href="http://www.vedaconsulting.co.uk/" rel="nofollow">Veda</a> has implemented this and can probably speak to best practices.</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-12T03:17:44.520Z
Answers.LastActivityDate: 2015-05-12T03:17:44.520Z
Answers.UserDisplayName: Jon G - Megaphone Tech
Answers.Body: <p>And another <a href="https://civicrm.org/blog/agilewarejustinfreeman/how-many-members-did-we-have-date-how-many-times-has-member-renewed-how" rel="nofollow noreferrer">solution</a> to check out using the <a href="https://civicrm.org/blog/agilewarejustinfreeman/how-many-members-did-we-have-date-how-many-times-has-member-renewed-how" rel="nofollow noreferrer">CiviCRM Data Processor Extension</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2021-11-23T04:27:14.363Z
Answers.LastActivityDate: 2021-11-23T04:27:14.363Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>In Drupal you can use Views and Aggregation to spit out a count of Memberships on any particular date, pretty much as per Paul's description, you set the Join Date to be 'before' and the End Date to be 'after'</p>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-02-15T00:24:14.643Z
Answers.LastActivityDate: 2017-02-15T00:24:14.643Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>The <a href="https://github.com/fuzionnz/nz.co.fuzion.historicmembershipdata" rel="nofollow noreferrer">nz.co.fuzion.historicmembershipdata extension</a> offers this feature</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-10-24T08:27:36.980Z
Answers.Text: Is that extension still working with PHP8 and Smarty updates, etc.?
Answers.UserDisplayName: Graham
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2024-10-25T09:17:25.260Z
Answers.Text: honestly don't know
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-11-23T21:08:36.240Z
Answers.LastActivityDate: 2021-04-12T23:10:58.973Z
Answers.LastEditDate: 2021-04-12T23:10:58.973Z
Answers.UserDisplayName: petednz - fuzion
Answers.Body: <p>I opened a proposal to add a new feature for SearchKit to handle a generic &quot;entity active period&quot; that I think covers the scope of this question. See <a href="https://lab.civicrm.org/dev/core/-/issues/3878" rel="nofollow noreferrer">SearchKit: Add ability to search and filter by active periods</a></p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2022-10-05T17:33:32.650Z
Answers.LastActivityDate: 2022-10-05T17:33:32.650Z
Answers.UserDisplayName: Francesc Bassas i Bullich
Answers.Body: <p>Since the status is based upon the start and end date and changes with the cron job, can you just leave the status off and select the date range.  Anyone who had a start date before the date in question and an end date after that date should have been a member on that date.  </p>
  
  <p>So...
  Select custom date range for start.<br>
  For From leave blank (the beginning of time).<br>
  For To: put the date in question.  </p>
  
  <p>Select custom date range for end.<br>
  For From put the date in question plus one day.<br>
  For To: leave blank.</p>
  
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-28T00:02:10.157Z
Answers.Text: I wonder if this needs slightly finessing. "Anyone who had a start date before the date in question and an end date after that date should have been a member on that date" sounds right, but if someone had one membership that ended before date X and another that started after then I think they would still be counted. To avoid this the search should be done on Memberships not on Contacts. Thoughts?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 4.0
Answers.CreationDate: 2020-07-28T00:06:17.997Z
Answers.Text: Also if your membership has a Fixed Start Date, do you need to base your search on Member Since Date rather than Member Start Date, since someone might join in july 1, their Start Date would be April 1, and if you are trying to identify members as of June 30 they would be included incorrectly?
Answers.UserDisplayName: petednz - fuzion
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2015-05-12T01:17:06.157Z
Answers.LastActivityDate: 2015-05-12T01:17:06.157Z
Answers.UserDisplayName: Paul-Tahoe
Body: <p>My client needs to know:</p>
  
  <ul>
  <li>How many members did we have on date x</li>
  <li>Who were the members on date x</li>
  </ul>
  
  <p>Using "Find memberships" it appears that, though the UI allows it, combining the membership status filter with any of the date filters will return misleading/wrong results. It seems that the query there is using whatever value is in the membership status field at the time you run the report to evaluate against the filter. (Is there a way to tell what query CiviCRM is running?)</p>
  
  <p>How can I best deliver this historical information to my client.</p>
  
  <p>The site in question is running CiviCRM 4.4.14 with Drupal 7.36.</p>
  
Tags: <civimember><civireport>
Title: Is it possible to get point-in-time membership data?
ThreadText: # Is it possible to get point-in-time membership data?
  
  (Asked by: username_0 on 2015-05-11)
  
  My client needs to know:
  
  * How many members did we have on date x
  * Who were the members on date x
  
  Using "Find memberships" it appears that, though the UI allows it, combining the membership status filter with any of the date filters will return misleading/wrong results. It seems that the query there is using whatever value is in the membership status field at the time you run the report to evaluate against the filter. (Is there a way to tell what query CiviCRM is running?)
  
  How can I best deliver this historical information to my client.
  
  The site in question is running CiviCRM 4.4.14 with Drupal 7.36.
  
  **Tags:** civimember, civireport
  
  
  ---
  
  ## 6 answers
  
  ### Answer by username_5 on 2015-05-12. Score: 8 | Accepted answer
  
  Since the status is based upon the start and end date and changes with the cron job, can you just leave the status off and select the date range. Anyone who had a start date before the date in question and an end date after that date should have been a member on that date.
  
  So...
  Select custom date range for start.  
  For From leave blank (the beginning of time).  
  For To: put the date in question.
  
  Select custom date range for end.  
  For From put the date in question plus one day.  
  For To: leave blank.
  
  > **Comments:**
  > **username_2**: I wonder if this needs slightly finessing. "Anyone who had a start date before the date in question and an end date after that date should have been a member on that date" sounds right, but if someone had one membership that ended before date X and another that started after then I think they would still be counted. To avoid this the search should be done on Memberships not on Contacts. Thoughts?  
  > **username_2**: Also if your membership has a Fixed Start Date, do you need to base your search on Member Since Date rather than Member Start Date, since someone might join in july 1, their Start Date would be April 1, and if you are trying to identify members as of June 30 they would be included incorrectly?  
  
  ---
  
  ### Answer by username_1 on 2015-05-12. Score: 3
  
  username_5's answer is the correct one for your use case. If you need point-in-time queries generally, consider putting Civi data in a data warehouse that supports OLAP cubes. Parvez Saleh at [Veda](http://www.vedaconsulting.co.uk/) has implemented this and can probably speak to best practices.
  
  
  ---
  
  ### Answer by username_2 on 2021-11-23. Score: 1
  
  And another [solution](https://civicrm.org/blog/agilewarejustinfreeman/how-many-members-did-we-have-date-how-many-times-has-member-renewed-how) to check out using the [CiviCRM Data Processor Extension](https://civicrm.org/blog/agilewarejustinfreeman/how-many-members-did-we-have-date-how-many-times-has-member-renewed-how)
  
  
  ---
  
  ### Answer by username_2 on 2017-02-15. Score: 1
  
  In Drupal you can use Views and Aggregation to spit out a count of Memberships on any particular date, pretty much as per Paul's description, you set the Join Date to be 'before' and the End Date to be 'after'
  
  
  ---
  
  ### Answer by username_2 on 2020-11-23. Score: 1
  
  The [nz.co.fuzion.historicmembershipdata extension](https://github.com/fuzionnz/nz.co.fuzion.historicmembershipdata) offers this feature
  
  > **Comments:**
  > **username_3**: Is that extension still working with PHP8 and Smarty updates, etc.?  
  > **username_2**: honestly don't know  
  
  ---
  
  ### Answer by username_4 on 2022-10-05. Score: 1
  
  I opened a proposal to add a new feature for SearchKit to handle a generic "entity active period" that I think covers the scope of this question. See [SearchKit: Add ability to search and filter by active periods](https://lab.civicrm.org/dev/core/-/issues/3878)
  
  
  ---
  
  