Hi!
For the past week, I have had quite the struggle trying to get this accurate and in truth, I still have a long way to go, hence this hopefull post :-).
PROBLEM AND 'TERMS':
- My dataset has 2000 unique 'member_ID'.
- All of these have a column for "Membership_Start_Date" and one for "Membership_End_Date".
- Some of the "End_Dates" are obviously null, since they are still members.
- The data traces back to 10-12-2017 and is updated in SQL every 20th hour (thus, I need the ability to capture it ongoingly too)
I would like to know how many members we have had on each specific date since the 10-12-2017 and in all relevant future.
My own shortcommings:
1) I tried "Generate rows" in order to add a column (called "Sign_up_Date") to my data set that listed all the dates between first datapoint and today.
2) Then I applied a logic to specify when a line should be counted as a member based on "Sign_Up_Date".
Logic:
IF "Sign_Up_Date" <= "Membership_Start_Date"
AND
"Sign_Up_Date" <= "Membership_End_Date"
OR IsNull ("Membership_End_Date")
Then "Member" else "Previous Member"
Endif
However, this doesn't summarize by each day, rather it finds whether or not that specific line is true or false... AS seen here:
| Membership_ID | Start_date | End_date | Sign_up_Date | Outcome | What I HOPED for |
| 1 | 10/12/2017 | 10/10/2018 | 9/1/2018 | Member / TRUE | 5 (all members at Sign_up_Date) |
| 2 | 10/25/2017 | null | 10/1/2018 | Member / TRUE | 5 |
| 3 | 11/01/2018 | null | 11/15/2018 | Member /TRUE | 4 |
| 4 | 1/1/2018 | 12/31/2018 | 1/1/2019 | Previous Member / FALSE | 2 |
| 5 | 2/2/2018 | 11/16/2018 | 4/1/2019 | Previous Member / FALSE | 2 |
THANK YOU ALL IN ADVANCE! 
