New Customers / Lost Customers - 2 different filters on lost customers

DAX DAX Calculations
1

Practice Data Set.pbix (400.5 KB)
I’ve watched all the videos on lost customers and can’t seem to apply the concepts to my current requirement.

I need a count of the lost customers.

1 - I have monthly customers that are filtered by contract name. They are considered lost if the previous month did not have any revenue for them.

2 - I have quarterly customers that are also filtered by contract name. They are considered lost if they are no revenue in the current quarter.

My biggest problem is I need this evaluated on a month to month basis. I can’t seem to figure out the formula for this. I have attached a practice dataset of my current file. Every time I do a formula, it counts all my customers as lost month to month and then regained the following month.

New Customers

  1. I have a formula for new customers in there but its not working how I would like. Its basically taking their first invoice and only counting that revenue as new customer. I would like it to count the whole months revenue for that customer as new customer revenue.

Ideally, I would be able to see a stacked chart of new customer / lost customer / current customers. Furthermore, I would like to know the net gain per month of new vs lost.

2

Anyone? Any idea?

3

@Rich Post an Excel file containing a table containing Year + Month and the number expected.

4

Sample Lost Customer.xlsx (11.2 KB)

Thanks for responding. See attached spreadsheet. Is that what you’re looking for?

5

Bumping this post for more visibility from our experts and users.

6

Hi @Rich - Your spreadsheet is not in sync with your PBIX file. In spreadsheet, you have given Company Id and Contract name but that is not available in PBIX. Can you share proper PBIX with correct data.

Also, please confirm the condition of lost customer. For monthly, it is mentioned if Customer has not done any purchase in previous month. What if purchase in current month, then customer will still be active right. Same will be for Quarter.

Thanks
Ankit J

7

Hi @ankit, Thank you for evaluating Rich’s PBIX File and offering a solution.

Hi @Rich,

Did the response from Ankit help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need? Also, he requested the proper PBIX with the correct data, to further assists you with this inquiry.

If it did, please mark his answer as the SOLUTION.

Thank you

8

See attached.
Steph’s Books - Dashboard.pbix (3.6 MB)

Monthly

  • Current customer has an invoice in current month.
  • Lost if they have no invoice in current month

Quarterly

  • Current customer has invoice in current quarter
  • Lost if they have no invoice in current quarter

It would be an invoice generated from the Billing Cycle data set. The commencement date is when the invoice is generated.

Just need to focus on Summary page.

9

Hi @Rich - Based on your description, formula seems correct. Only thing is for lost customer you need to change

Lost Customer - Fixed Period =
var _currdate = max ( ‘Date Table’[Date] )
var _salesthisperiod = [Total Revenue]
var _customers =
ADDCOLUMNS(
dimCompany,
“Sales Until Now”,
CALCULATE (
[Total Revenue],
DATESINPERIOD(
‘Date Table’[Date],
_currdate,
-1,
MONTH ) ) ,
“Running Total Sales”,
[Running Total Sales]
)
var _lostcustomers =
FILTER(
_customers,
[Running Total Sales] > 0 && [Sales Until Now] = 0 )

RETURN
if ( _salesthisperiod = 0 ,
COUNTROWS( _lostcustomers )
)

Thanks
Ankit J

10

Hello @Rich ,

Did the response from @ankit help solve your query?

If not, can you let us know where you’re stuck and what additional assistance you need?

If it did, please mark the answer as the SOLUTION.

Thank you

11

Hi @Rich

Due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We request you to kindly take time to answer the Enterprise DNA Forum User Experience Survey,.

We hope you’ll give your insights on how we can further improve the Support forum. Thanks!