Skip to main content
cancel
Showing results for 
Search instead 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

Reply
VModani
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 NameSale Date
A2/11/2021
B

2/11/2021

A3/11/2021
C4/11/2021
D5/11/2021
B7/11/2021
B7/11/2021
D7/11/2021
C8/11/2021
E9/11/2021
E10/11/2021

 

Thanks

1 ACCEPTED SOLUTION
David-Ganor
Resolver II
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

 

View solution in original post

5 REPLIES 5
ashu131813
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?

David-Ganor
Resolver II
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

 

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
Super User
Super User

Hi @VModani ,

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

tomfox_0-1644049367030.png

 

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

 



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

serpiva64
Super User
Super User

Hi,

in Power Query you can obtain this:

serpiva64_0-1644048958966.png

To obtain it :

serpiva64_1-1644049076509.png

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

Then you Group by :

serpiva64_2-1644049196125.png

and finally expand:

serpiva64_3-1644049245772.png

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"

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors