cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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 IX
Super User IX

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!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

2 REPLIES 2
Highlighted
Super User IX
Super User IX

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!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Highlighted
Helper I
Helper I

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
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors