cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Calculate active customers on a month-by-month basis

Hi All,

 

I have a requirement to calculate the total number of customers who have an Active subscription, based on the Date of Purchase, Date of Expiry and the Customer Number. Unfortunately the requirement is both on a tight time scale and I'm only just getting to grips with PowerBI.

 

I have attempted to use the solution(s) here:

 

https://community.powerbi.com/t5/Desktop/Calculate-active-subscribers-with-Initial-and-Expiration-da... 

and

https://community.powerbi.com/t5/Desktop/distinct-count-of-active-customers-for-each-month/td-p/4015... 

 

However, neither of them have given accurate data - for example, I know the current count today is approximately 90,000 Customers, whereas using either of those methods I didn't get a result higher than 10,000 for the Month of July 2020.

 

The relevant columns that I'm using are:

 

Customer Number (unique integer)

Purchase Date (dd-mm-yyyy hh-mm-ss, does have some NULL values)

Expiration Date (dd-mm-yyyy hh-mm-ss, If date is set and in the past, then Subscription has expired, if it's NULL or in the future, then it's still active)

The Subscriptions can have time periods between the purchase date and expiration date of up to 10 years (which is where I believe the 2 methods I tried above didn't work)

 

A Customer can also have multiple subscriptions

 

Sample Data:

 

Customer Number       Purchase Date            Expiration Date

1                                   10/05/2015                   NULL

1                                   15/01/2017                   15/01/2018

1                                   30/06/2020                   NULL

2                                    07/12/2015                   NULL

3                                   16/05/2015                   27/10/2019

 

So for the 05/2015 - Active Customer count would be 2, for 12/2015 - Active Customer count would be 3, for today, the active customer count would be 2.

 

Any assistance would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Calculate active customers on a month-by-month basis

@PowerBIPilgrim ,

See if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IV
Super User IV

Re: Calculate active customers on a month-by-month basis

@PowerBIPilgrim ,

See if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
New Member

Re: Calculate active customers on a month-by-month basis

I've had to make a number of changes to the solution (since it doesn't factor in that a customer can have multiple subscriptions at any one time)

 

But after adding an additional field with a distinctcount, filtering by the other measured fields - I was able to get an accurate number.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors