Counting Active in non calendar date range

[MarionC]

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

[MarionC]

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.

[v-kaiyue-msft · Accepted solution]

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.

[audreygerred]

Hi! Check out this YouTube video - it has helped me with something similar: https://youtu.be/pQ9eSnfAhnc?si=YZBEyoJzdS_HS3pV