Number of Active Customers (subject to number of active products), per month

[jreynolds]

Hi,

I’m trying to report on the number of customers who have at least one active “product” associated with their “customer_ID”, by month for the last few years. It doesn’t matter which product. We create a “create_date” or “cancel_date” record for each corresponding event for each customer – see below. So long as number of cancellations for a given month don’t exceed the total creations for all previous months combined, the customer should be considered “active”.

For example:

ACTVE:  a customer has 5 products in June and cancels 3 products in July

INACTIVE: a customer who has 5 products in June and cancels 5 products in July

In effect, I need a rolling count of all “create_dates” by customer to compare against each historic month to see if the customer should be considered “inactive”, or at least that’s one way of doing it.

I’ve tried creating this a few different ways (calculated columns etc.) but am not have any luck.

I’d be grateful for any suggestions.

[Ashish_Mathur]

Hi,

It would be ideal if you could share a dataset (which can be pasted in MS Excel) and share the reuslt of the dataset that you share.

[jreynolds]

Hi Ashish,

Thanks for your reply. I'm happy to provide a subset of the dataset as per the image in my original post. Can you confirm if that's what you're asking for,

Thanks again

[Ashish_Mathur]

Hi,

Is this your expected result.  You may download my PBI file from here.

Hope this helps.

[jreynolds]

Hi Ashish,

Thanks again for your help.

I've reviewd your file and it's clear you've progressed things in a much better way that I had 🙂 It's been very helpful

I've applied the file to my production data and have one question:  As per my original post, I was wanting to plot the number of active customers by year/month. When plotting the results by month/year (line chart, 'attriute' as ledgend - see below), I'm seeing the cancelleddate and createddate values. To calculate the number of active customers, do I need to subtract the number of cancelledddate records from the createdate records?

Apologies if I'm missing something

[Ashish_Mathur]

Hi,

Remove attribute from the Legend section.

[jreynolds]

Removing the attribute from the Legend results in the sum of ceated and cancelled being added together, which doesn't seem right. The Measure isn't part of the chart (can't be added), so it seem as if it's just a count of customer_id and not taking into accoun the logic created in the Measure

March 2017 below is a good exmaple of ceated and cancelled just being added together. First image is the without the Legend, second is with.

Thanks again, Ashish. I really appreciate your help.

 

[Ashish_Mathur]

Hi,

In the Line chart, drag Date from Calendar Table to the Axis and remove Quarter and Day.  Drag Customer ID from the Data Table to the Values section and select the functinon as Distinct Count there.  In the visual filter section, drag the Measure and filter the measure to show Active only.

[jreynolds]

Thanks again Asish for your help

Having spent time looking at this further with production data, I can see it’s not representing the data correctly. For example, I know for a fact that we have thousands of customers with at least one product in any given month, yet when drilling down to the month level with the pbi file you provided, it’s only showing 200-300 for a giving month.

Is it possible the file is reporting only ‘new’ customers for that month instead of including the historic customer count as well??

[Ashish_Mathur]

Hi,

I cannot say why my formula isn't working.  Let's start with a small dataset.  On that small dataset, share your expected result (in a simple table format).  Once i frame a working formula to get your result, we will then build the visual you want.

[jreynolds]

Hi again,

I'll PM you a file containing demo data that I hope explains what I've after. Note that the cusotmer ID has change to a new format (concat of streetname/postcode).

The goal is to have the total number of customers who, in a given month, have at lease one active product. But the total needs to include all previous months (cumulative). Ideally it would be good to have a current Month column and a Cumulative column.

Hope that makes sense.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[jreynolds]

Hi Ashish,

You're absolutely correct! I included the accumulative row at the last minute as a nice to have and clearly didn't give it enough thought. 

I think I found the issue with the PBI file. It's not counting Unique IDs where they span multiple months. If you look at the file I sent via PM you'll see what I mean. The PBI is miscounting (not counting the month of April).

Are you able to take a look?

Thanks again, Ashish. You've been so helpful.

[jreynolds]

You're absolutely correct! I included the accumulative row at the last minute as a nice to have and clearly didn't give it enough thought. 

I think I found the issue with the PBI file. It's not counting Unique IDs where they span multiple months. If you look at the file I sent via PM you'll see what I mean. The PBI is miscounting (not counting the month of April).

Are you able to take a look?

Thanks again, Ashish. You've been so helpful.

[jreynolds]

File can be downloaded here

[Ashish_Mathur]

Hi,

My formula works fine.  Check the scrrenshot of the PBI file which i shared with you yesterday.  The figure for April is 2.

[jreynolds]

Hi Ashish,

Thanks so much for the file. It's certianly looking like it's better quality than what I was able to produce 😕

I'll take a look at the file now and let you know. The key result is being able to plot the total number of "active" customers by month. I'll see post back shortly.

Thanks again!