Counting Subscription Users

[BloodBorne]

Hi All,

Thanks for your help in advance.

This is my dataset below

Customer ID	Customer Registration Date	Customer Subscription Date	Customer Expiration Date
1	01/05/2024	01/05/2024
2	01/05/2024	01/05/2024	01/06/2024
3	01/05/2024	01/05/2024	01/06/2024
4	02/05/2024	02/05/2024	02/05/2025
5	02/05/2024	02/05/2024	02/06/2024
6	02/05/2024	02/05/2024	02/06/2024
7	02/05/2024	02/05/2024	02/06/2024
8	02/05/2024	02/05/2024	02/05/2025
9	02/05/2024	02/05/2024
10	03/05/2024	03/05/2024
11	04/05/2024	04/05/2024	04/06/2024
12	04/05/2024	04/05/2024

The blank in the Customer Expiration Date means the subscription is still active.

My question is what is the DAX formlua to know the number of ACTIVE subscribers in a given time period. I'd like to use a date range slicer. In other words, if the date range slicer is set between  03/06/2024 - 30/06/2024, the answer should be 7, because only Customer ID 1, 4, 8, 9, 10, 11, 12 have active subscription because their Customer Expiration Date is either after 03/06/2024 or is blank.

Hope this is clear.

Thanks again, much appreciated.

Kind Regards

[Anonymous · Accepted solution]

Hi @BloodBorne ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a DateSlicer Table.

DateSlicer = CALENDAR(MIN('Table'[Customer Expiration Date]),MAX('Table'[Customer Expiration Date]))

(3) We can create a measure. 

Count = 
var _a= COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Customer Expiration Date]>=MIN('DateSlicer'[Date])))
var _b= COUNTROWS(FILTER(ALLSELECTED('Table'),[Customer Expiration Date]=BLANK()))
RETURN _a+_b

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous · Accepted solution]

Hi @BloodBorne ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a DateSlicer Table.

DateSlicer = CALENDAR(MIN('Table'[Customer Expiration Date]),MAX('Table'[Customer Expiration Date]))

(3) We can create a measure. 

Count = 
var _a= COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Customer Expiration Date]>=MIN('DateSlicer'[Date])))
var _b= COUNTROWS(FILTER(ALLSELECTED('Table'),[Customer Expiration Date]=BLANK()))
RETURN _a+_b

(3) Then the result is as follows.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BloodBorne]

Hi,

Thank you so much. It works perfectly.

By the way, is it possible to modify it so that it shows the number of active subscribers for the last day on the month?

I'd like to plot a time series chart.

Month-Yr	Number of Active Subscribers
Jan 2024	5
Feb 2024	6
Mar 2024	8
Apr 2024	10

Basically, what's the number of active subscribers on the last day of each month?

Thanks again 🙏

[Anonymous · Accepted solution]

Hi @BloodBorne ,

We can update the DateSlicer table.

DateSlicer = ADDCOLUMNS( CALENDAR(DATE(2024,1,1),DATE(2025,12,31)),"Year",YEAR([Date]),"Month_num",MONTH([Date]),"Month",FORMAT([Date],"mmm"))

Then we can create a measure.

Count2 = 
var _day= EOMONTH(DATE(MAX('DateSlicer'[Year]),MAX('DateSlicer'[Month_num]),1),0)
var _a= COUNTROWS(FILTER(ALLSELECTED('Table'),'Table'[Customer Expiration Date]>=_day))
var _b= COUNTROWS(FILTER(ALLSELECTED('Table'),[Customer Expiration Date]=BLANK()))
RETURN _a+_b

As of the last day of June 2024, there were six active users.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BloodBorne]

Thank you so much. Wish I could buy you a drink to show my appreciation. 

[BloodBorne]

Any help on this would be appreciated. Thanks