Forum Discussion

BloodBorne's avatar
BloodBorne
Frequent Visitor
2 years ago
Solved

Counting Subscription Users

Hi All,

 

Thanks for your help in advance.

 

This is my dataset below

Customer IDCustomer Registration DateCustomer Subscription DateCustomer Expiration Date
101/05/202401/05/2024 
201/05/202401/05/202401/06/2024
301/05/202401/05/202401/06/2024
402/05/202402/05/202402/05/2025
502/05/202402/05/202402/06/2024
602/05/202402/05/202402/06/2024
702/05/202402/05/202402/06/2024
802/05/202402/05/202402/05/2025
902/05/202402/05/2024 
1003/05/202403/05/2024 
1104/05/202404/05/202404/06/2024
1204/05/202404/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

 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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's avatar
    Anonymous
    2 years ago

    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. 

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      BloodBorne
      Frequent Visitor
      2 years ago

      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-YrNumber of Active Subscribers
      Jan 20245
      Feb 20246
      Mar 20248
      Apr 202410

       

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

       

      Thanks again 🙏

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        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.