New Customer Analysis

[Hesham]

Hiya, I am trying to identify new customers within the current reporting month, but the Dax calculation is not returning the right outcome.

Attached is sample data that holds 8 columns:

“Service Start” column indicates a new sale/service within the reporting month column. The expected outcome to show how many new customers and values:

The Dax formula I tried is:
New Customers =
VAR
CustomersList = VALUES( Data[ID] )
RETURN
CALCULATE( COUNTROWS( VALUES( Data[ID] ) ),
FILTER(
CustomersList,
CALCULATE( COUNTROWS( Data ),
FILTER( ALLSELECTED( ‘Date’), ‘Date’[Date] < MIN( ‘Date’[Date] ) ) ) = 0 ) )
However, the table outcome:

Not sure where is my error?
ThanksSample to New Customer.xlsx (16.8 KB) Sample New Customer.pbix (132.2 KB)

[Melissa]

Hi @Hesham,

There was a similair requirement not long ago you can find that thread here:

Lost Customer Count Shows 3 Months Late

My customers are pretty simple, they bill on the first of each month. I am trying to count any customer that billed last month but did not in the current month as lost. I tried the formula from the video “Analyze Who Your Lost Customers Are”. Depending on how I tweak the dates, I either get no data or a correct count except the count is off by 3 months. In other words, I lost a customer in September but it counts as lost in December. Is there a way to tweak the formula to make it do this? I a…

Also be sure to check out Sam’s video on this topic.
https://forum.enterprisedna.co/t/who-are-the-lost-customers-advanced-analytics-with-power-bi-dax/2969
.
I’ve added a MonthOffset to your Date table:

InsertMonthOffset = Table.AddColumn(AddFY, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(DateTime.FixedLocalNow()))) + Date.Month(Date.From(DateTime.FixedLocalNow()))))

And expanded the date range in your calendar.

.
Next I created this measure:

New Customers v2 = 
VAR ThisMonth = LOOKUPVALUE( 'Date'[MonthOffset], 'Date'[MonthInCalendar], SELECTEDVALUE('Date'[MonthInCalendar]))

VAR PreviousCustomers =
    CALCULATETABLE ( VALUES ( Data[Customer] ),
        FILTER ( ALL( 'Date' ),
        'Date'[MonthOffset] <= ThisMonth -1
        )
    )
VAR NewCustomers =
    CALCULATETABLE ( VALUES ( Data[Customer] ),
        FILTER ( ALL ( 'Date' ),
        'Date'[MonthOffset] = ThisMonth
        )
    )
RETURN
    COUNTROWS ( EXCEPT ( NewCustomers, PreviousCustomers ) )

.
And this is the result

I hope this is helpful, here’s the solution file:
eDNA - Sample New Customer.pbix (140.4 KB)

[Hesham]

Hi @Melissa

Thanks for replying. Unfortunately, the new V2 is getting me the same output as my first Dax try (when I add the “MonthinCalendar” in columns).

New Customer rule = New sales in the current reporting month only, and not before it. Here is an example:

If you look at the reporting month in Jan-15, we have 7 records. Of which 4 is new sales only, and the remaining 3 records belong to an old sale (they happened in Dec & Oct 2014 and not Jan 15).

So what I am expecting to see on Jan 15 as new customers are only 4 records and not the full records (7).

[Melissa]

@Hesham, I’m hoping you can help me out…

So I can identify those 4 records in Jan 2015, using this measure:

New Customers v3 = 
VAR ThisMonth = LOOKUPVALUE( 'Date'[MonthInCalendar], 'Date'[Date], SELECTEDVALUE(Data[Reporting Month] ))

VAR Customers1 =
    CALCULATETABLE( VALUES( Data[Customer] ),
        FILTER( ALLSELECTED( 'Date' ),
        'Date'[MonthInCalendar] = ThisMonth
        )
    )
VAR Customers2 =
    CALCULATETABLE( VALUES( Data[Customer] ),
        USERELATIONSHIP( 'Date'[Date], Data[Service Start] ),
        FILTER( ALLSELECTED( 'Date' ),
        'Date'[MonthInCalendar] = ThisMonth
        )
    )
RETURN
    COUNTROWS( INTERSECT( Customers1, Customers2 ) )

.
Here’s the result but that’s how far it goes I’m afraid…

Looking at what you described here:

@Hesham

If you look at the reporting month in Jan-15, we have 7 records. Of which 4 is new sales only, and the remaining 3 records belong to an old sale (they happened in Dec & Oct 2014 and not Jan 15).

So what I am expecting to see on Jan 15 as new customers are only 4 records and not the full records (7).

.
And the remaining records… None of the other Service Start dates line up with the Reporting Month

eDNA - Sample New Customer.pbix (179.2 KB)

[Hesham]

@Melissa Appreciate your time and effort on this question. It is interesting that your solution does work on the first reporting month only, and not for the remaining months. I will play around and see if I can tweak the code to get the other months to show (Feb-15: 2 new customers, Mar-15: only one new customer). Once I manage to get a solution, I will share the update in here.

Thanks again for your time.

[ScottTPA]

Just fyi, @Melissa solution worked perfectly for my situation.

[Melissa]

@Hesham,

I’m happy to help but it’s important for me to understand the logic and every element in play.

At this time we have two conflicting rules:
First in the case of Jan 2015 we exclude all Service Start dates that predate the Reporting Month
But then in all other cases we only have Service Start dates that predate the Reporting Month

So there must be other contributing factors I’m not aware of… Can you help me out with that?

Thanks!

[Hesham]

@Melissa

Hopefully, I can confirm the logic.

@Melissa

First in the case of Jan 2015 we exclude all Service Start dates that predate the Reporting Month

Correct, and that should apply to all the reporting months. Meaning: the new customer is identified whenever a start service is within the selected reporting month. Should the customer have a starting service before the selected month, this would be an old customer.

Based on the above, Jan 15 should have 4 new customers, Feb 15 should have 2 new customers, and May 15 would have only one new customer.

[Melissa]

@Hesham

Feb 15 should have 2 new customers, and May 15 would have only one new customer.

@Hesham

New Customer rule = New sales in the current reporting month only, and not before it.

.
Can you identify the rows meeting this criteria for those reporting months? Thanks for your patience.

[Hesham]

@Melissa

Can you identify the rows meeting this criteria for those reporting months? Thanks for your patience.

I believe that the “Start Service” is not showing with the right format DDMMYYYY.

[Hesham]

Happy to say that I have found the solution and it’s very simple!

Using any of the following:

1- Calculated column
CountCustomers = IF ( EOMONTH(Data[Service Start],0) < EOMONTH(Data[Reporting Month],0), 0, 1 )

2- Measure
Customer Status = VAR CountCustomers = //counts old sales per customer CALCULATE ( SUM ( Data[CountCustomers] ), ALLEXCEPT ( Data, Data[Customer]) ) RETURN //if CountCustomers = 0 then new customer IF ( CountCustomers = 0, "Old", "New" )

Here is the pbix file Sample New Customer V3.pbix (132.0 KB) .

Thank you @Melissa, for your support. I think the main issue was the start date column was showing in US format, instead of UK format.

[Melissa]

Glad to hear it’s sorted.
And thanks for sharing!

[EnterpriseDNA]

Hi @Hesham, I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!