Hello Community,
I want to calculate running total of count of active members every month, I have Member id, effective day, terminate day and Paid Amount. (If a member has 12/31/2999 as a termination date then member is active)
If a member's effective date and terminate date are in same month, that member will be counted as active only for that month, when we count members for month of february it should include the count of active member from january excluding the terminated members and count of active members in month of feb.
For example:
Month Count of active members
January 2017 20
Feb 2017 18+40=58 (because 2 members were terminated in january)
March 2017 50 + 30=80 (because 8 members were terminated in february)
User can use any slicer to see the count of member, for example bewteen slicer if user selects date range from 01/1/2017 to 03/31/2017, the total count should 20+58+80= 158.
Sample Data:
| Member ID | ClientName | Effective date | Term date | Paid Amount |
| 9705542 | HHC | 1/1/2017 | 1/29/2017 | $ 4,562.00 |
| 9732311 | CCA | 2/14/2017 | 12/31/2999 | $ 5,000.00 |
| 2075200 | CCA | 2/5/2017 | 12/31/2999 | $ 3,897.00 |
| 9675600 | YHA | 2/24/2017 | 12/31/2999 | $ 3,355.00 |
| 4037701 | YHA | 2/13/2017 | 12/31/2999 | $ 9,876.00 |
| 40500 | YHA | 1/14/2017 | 12/31/2999 | $ 5,467.00 |
| 5613800 | ABC | 1/19/2017 | 12/31/2999 | $ 10,000.00 |
| 1888800 | XYZ | 3/22/2017 | 12/31/2999 | $ 7,658.00 |
| 9675398 | XYZ | 3/12/2017 | 4/9/2017 | $ 7,500.00 |
| 9705604 | XYZ | 3/15/2017 | 12/31/2999 | $ 5,487.00 |
| 831701 | DAS | 4/13/2017 | 12/31/2999 | $ 3,400.00 |
| 9657072 | DAS | 4/18/2017 | 12/31/2999 | $ 4,900.00 |
| 67512 | DAS | 4/19/2017 | 12/31/2999 | $ 8,400.00 |
| 56478 | HHC | 1/23/2017 | 12/31/2999 | $ 5,000.00 |
| 54376 | HHC | 2/27/2017 | 3/10/2017 | $ 3,245.00 |
| 45679 | ABC | 3/21/2017 | 12/31/2999 | $ 4,356.00 |
| 2135 | ABC | 4/23/2017 | 12/31/2999 | $ 1,205.00 |
