How to identify new customers

[VModani]

I have a sales table with reoccuring customers. I need to identify new customers over the entire table, not restricted by a time frame.

The Distinctcount(Customers) function counts distinct customers each day, I want to find when did we get a completely new customer.

Sample Table : I only need the fields in bold to plot the customer-base on a line graph

Customer Name	Sale Date
A	2/11/2021
B	2/11/2021
A	3/11/2021
C	4/11/2021
D	5/11/2021
B	7/11/2021
B	7/11/2021
D	7/11/2021
C	8/11/2021
E	9/11/2021
E	10/11/2021

Thanks

[ashu131813]

I have aquery regarding the same.I need New Customers that are added in my website in a year.For that I used this DAX Query -

New customers = 
VAR currentCustomers = VALUES('Sales table'[customer])
VAR currentDate = MIN('Sales table'[date])

VAR pastCustomers = CALCULATETABLE(VALUES('Sales table'[customer]), 
    ALL('Sales table'[date].[Month],'Sales table'[date].[MonthNo],'Sales table'[date].[Year])
    , 'Sales table'[date]<currentDate)

VAR newCustomers = EXCEPT(currentCustomers,pastCustomers)
RETURN COUNTROWS(newCustomers)

But I want to filter only those Customers who came from certain region ie I want to add Filter in the above query like
FILTER(Geo_Data, Geo_Data[Region] IN {
"East",
"North-East",
"North"
}
)

Is there any way to do this thing?

[David-Ganor · Accepted solution]

Hi @VModani ,

Generally, you can point out when was the first time a customer is shown on the table, and count thise rows or customers by that.

Please create a Calculated column as follows:

Customer first appearance=

Var this_row_customer=Table[Customer Name]

Var this_row_sale_date=Table[Sale Date]

Var first_appreance=Calculate (MAX(Table [Sale Date])

FILTER(Table, Table[Customer Name]=this_row_customer]))

Return

IF(this_row_sale_date=first_appreance, Table[Customer Name],"")

Then - on the visual - count the Table[Customer first appearance] by Sale Date.

Hope it helps

David

[VModani]

Thanks, this is working but showing +1 values for some reason. 

I ended up writing a SQL querey for it in the end which is working perfectly

[tackytechtom]

Hi @VModani ,

I got this solution here (table on the right):

I solved it by creating a new column with this DAX:

TomsColumn = 
VAR _minDate = 
CALCULATE ( 
    MIN ( 'Table'[Sale Date]), 
    FILTER ('Table', 'Table'[Customer Name]  =  Earlier([Customer Name]))
)

RETURN
IF ( 'Table'[Sale Date] = _minDate, 1, 0 )

After that I filtered on this column in the visual.

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom

[serpiva64]

Hi,

in Power Query you can obtain this:

To obtain it :

Don't consider first two steps i need to get your sample data.

Then you Group by :

and finally expand:

that' all.

If this post isuseful to help you to solve your issue consider giving the post a thumbs up and accepting it as a solution !

This is the query in advanced editor.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTLSNzTUNzIwMlSK1YlWcsIQAakxRhFxBoqYoIi4AEVMMcwxJyDigiECMtkCRcQVKGKJIWJogBCKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Customer Name" = _t, #"Sale Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer Name", type text}, {"Sale Date", type date}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"Customer Name"}, {{"AllRows", each _, type table [Customer Name=nullable text, Sale Date=nullable date]}, {"FirstSale", each List.Min([Sale Date]), type nullable date}}),
#"Expanded AllRows" = Table.ExpandTableColumn(#"Grouped Rows", "AllRows", {"Sale Date"}, {"Sale Date"})
in
#"Expanded AllRows"