new customers in a selected period

[giorgiokatr]

customer	purchase
a	1/1/2016
b	1/1/2016
c	1/2/2016
a	1/2/2016
d	1/3/2016
c	1/3/2016
e	1/3/2016

i would like to calculate the new customers.New customers are those who made a purchase and never purchaced again in the last 10 days

for example if i select 01/03/2016 new customers should be 3 and not 2 because customer c is considered as new (he purchaced at 01/03/2016 and before 19/02/2016   *10 days*)

[AlbertoFerrari]

There are many approaches to this scenario.

One option is to adapt the code shown here: https://www.sqlbi.com/articles/computing-new-customers-in-dax/ to your specific scenario. Basically, you will need to change the limits of the filter, so to take into account the 10 days. The code will not be super-fast, because of the dynamic boundaries.

Another option, much simpler, is to check - for each event - whether a similar one (same customer) happened earlier than 10 days before. This results in a simple flag in your table, that you can later use in a measure to filter sales to new customers.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Zubair_Muhammad]

Hi @giorgiokatr

Try this. Add 2 calculated Columns as follows

Last_Purchase =
CALCULATE (
    LASTDATE ( TableName[purchase] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[customer] ),
        TableName[purchase] < EARLIER ( TableName[purchase] )
    )
)

DaysDifference =
DATEDIFF ( TableName[Last_Purchase], TableName[purchase], DAY )

Now use this MEASURE to Count New Customers at any specific date

New Customers =
CALCULATE (
    COUNTROWS ( TableName ),
    FILTER (
        TableName,
        [DaysDifference] > 10
            || ISBLANK ( TableName[Last_Purchase] )
    )
)

[giorgiokatr]

thanks @Zubair_Muhammad @AlbertoFerrari and @Anonymous

@Zubair_Muhammad will this work if i have another column with products like this

customer	product	purchase
a	radio	1/1/2016
b	radio	1/1/2016
c	radio	1/2/2016
a	radio	1/2/2016
d	tv	1/3/2016
c	tv	1/3/2016
e	tv	1/3/2016

if i want to calculate the same but for tv will it work?

[v-yulgu-msft]

Hi @giorgiokatr,

Does Zubair_Muhammad's solution meet your requirement? If yes, would you please accept the helpful reply as an answer so that others having similar concern can find the solution more easily? If not, then, what the correct result of "New customer" should be when taking [Product] into account? Please provide more description.

Regards,

Yuliana Gu

[AlbertoFerrari]

If you rely on a calculated column, it will not work, as a calc col is computed statically. If you opt for a measure, then it will be completely dynamic and adapt to any selection, albeit a bit slower.

You can also refer to daxpatterns.com, where there is a whole section dedicated to new and returning customers (it is one of my favourite calculations, along with events in progress :))

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[danielpenafiel]

How about show attributes of the new clients like: by vendors or products, How I can aproach that?

[Zubair_Muhammad · Accepted solution]

@giorgiokatr

Modify the "Last Purchase" Calculated Column.

Change highlighted in red below

Last_Purchase =
CALCULATE (
    LASTDATE ( TableName2[purchase] ),
    FILTER (
        ALLEXCEPT ( TableName2, TableName2[customer], TableName2[product] ),
        TableName2[purchase] < EARLIER ( TableName2[purchase] )
    )
)

[Anonymous]

hi @giorgiokatr,

This blog will help you in setting this up the right way. http://www.daxpatterns.com/new-and-returning-customers/

Good luck!