cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## How to identify new customers

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

1 ACCEPTED SOLUTION
Resolver II

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

5 REPLIES 5
New Member

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?

Resolver II

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

Frequent Visitor

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

Super User

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

 Also happily accepting Kudos 🙂

 Feel free to connect with me on LinkedIn!

 #proudtobeasuperuser

Super User

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"