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.
