Forum Discussion
Counting Active in non calendar date range
I've read through a number of similar posts, but none quite fits my scenario, and having poked and prodded, I can't make them fit.
I have a USER table with Username, Creation date, Expiry date. Where Username is a string, Create date is a date/time and Expiry date is a date/time. The Expiry date could be blank.
e.g.
| Username | Creation Date | Expiry Date |
| Person A | 03/02/2023 | 05/06/2023 |
| Person B | 06/02/2023 | 07/02/2023 |
| Person C | 10/03/2023 | 09/05/2023 |
| Person D | 15/03/2023 | |
| Person E | 25/03/2023 | 26/03/2023 |
I have a BILLING table with Start Date, End Date, Billing Period. This represents our billing periods, which run from 26th of eachmonth to 25th of the next. Billing period is purely a numeric "label" to give the billing period a name of sorts.
e.g.
| Start Date | End Date | Billing Period |
| 26/01/2023 | 25/02/2023 | 1 |
| 26/02/2023 | 25/03/2023 | 2 |
| 26/03/2023 | 25/04/2023 | 3 |
| 26/04/2023 | 25/05/2023 | 4 |
| 26/05/2023 | 25/06/2023 | 5 |
| 26/06/2023 | 25/07/2023 | 6 |
The tables are not related.
What I would like to show is the number of Active accounts per billing period. Where an active account is deemed to be on or after the date of creation, and before or on the date of expiry.
This would result in a table that looks like this:
| Billing Period | Active Users | My explaination (not in the visual) |
| 1 | 2 | Person A and B both count |
| 2 | 4 | Person A, C, D and E were all created within the billing period, but person B expired during period 1 so is not counted. |
| 3 | 4 | Person A, C, D and E have days active in this billing period before their expiry date even Person E because they expire on the first day of this billing period, hence are still in it. |
| 4 | 3 | Person E expired in the previous billing period, leaving only A, C and D |
| 5 | 2 | Person C expired in the previous billing period, so only A and D are left |
| 6 | 1 | Only person D, with no expiry date, is left here. |
Hope this makes sense.
Thank you in advance for any help you may be able to offer.
Best,
Marion
Hi MarionC ,
Your solution is great, audreygerred . Here I have another idea that I want to share for reference.
You can create a measure to count users.Active Accounts = CALCULATE( COUNTROWS('USER'), FILTER( 'USER', 'USER'[Creation Date] <= MAX('BILLING'[End Date]) && (ISBLANK('USER'[Expiry Date]) || 'USER'[Expiry Date] >= MIN('BILLING'[Start Date])) ) )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- audreygerred
Super User
Hi! Check out this YouTube video - it has helped me with something similar: https://youtu.be/pQ9eSnfAhnc?si=YZBEyoJzdS_HS3pV
Hi MarionC ,
Your solution is great, audreygerred . Here I have another idea that I want to share for reference.
You can create a measure to count users.Active Accounts = CALCULATE( COUNTROWS('USER'), FILTER( 'USER', 'USER'[Creation Date] <= MAX('BILLING'[End Date]) && (ISBLANK('USER'[Expiry Date]) || 'USER'[Expiry Date] >= MIN('BILLING'[Start Date])) ) )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you both for your help! I was so close with my own explorations, but you nailed it, and gave me more information to help me learn.
