New and Recurring Customers

[H_insight]

Hello,

I have a sample data that contains 8 columns. What I am trying to do is to identify new customers withing the current reporting month, but my measure is not returning the right outcome.

New Customer rule = New sales in the current reporting month only, and not before it. In the below 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). “Service Start” column indicates a new sale/service within the reporting month column.

I tried the below Dax to get the new customers:
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 was:

The expected outcome to show how many new customers and values should be:

Sample pbix file

Any ideas?

Thanks

[danextian]

Hi @H_insight,

If I understand correctly, if the YYYYMMDD of  Service Start < Reporting Date, it is an old sale.  Try these calculated columns

OldSalesCount =
IF (
    VALUE ( FORMAT ( EOMONTH ( 'Table'[Service Start], 0 ), "YYYYMMDD" ) ) < 'Table'[ReportingDate],
    1,
    0
)

Customer Status =
VAR OldSalesCount =
    //counts old sales per customer
    CALCULATE (
        SUM ( 'Table'[OldSalesCount] ),
        ALLEXCEPT ( 'Table', 'Table'[Customer Number] )
    )
RETURN
    //if OldSalesCount = 0 then new customer
    IF ( OldSalesCount = 0, "new", "old" )

[H_insight · Accepted solution]

@danextian 

Thanks for your help on this. I have changed the OldSalesCount to:

OldSalesCount = 
IF (
     EOMONTH(Data[Service Start],0) < EOMONTH(Data[Reporting Month],0),
    0,
    1
)

and the result came as expected.

Cheers

[H_insight]

Hi @danextian ,

Correct, if the YYYYMMDD of Service Start < Selected Reporting Date then it's an old customer. If YYYYMMDD of Service Start is within the Reporting Date dates, then it's a new customer. Please bear in mind that reporting month will always be the end of the month, so the service started could fall within the month.

I have tried your Dax, but unfortunately not the right outcome.

The outcome should look like:

Thanks

[nhoward]

Hi Hesham,

We'll need to know the tables and relationships in your data model before we can help. 

[H_insight]

Hi @nhoward ,

Here is the PBIX link & the sample file.

The sample file holds a simple model of Data (fact) and Date table. Only one relationship between Reporting Month in Data table & Date in Date table.

Please let me know if you require any additional information.

Thanks

Hesham