First Time Customer List

[Anonymous]

Hello Everyone,

I have a column chart where I show a count of new customers by month with "month year" along the x-axis.

Similarly, I would like to create a matrix/list that shows the name of the new customers in each month (as opposed to count).

Below is an image of what I have already.

I've done a search of the community, but not had much luck with the keywords.

Any tips,

Thanks

Rich

[TomMartens · Accepted solution]

Hey,

yes it could be done, but some for effort is necessary ...

EXCEPT returns a table, instead of using this as the 1st argument of concatenatex store this into a variable.

Then use something like this

CONCATENATEX(
FILTER(ALL('<customertable>')
, '<customertable>'[customer key] IN tablevariable
)
, concatenate all the column values
, new line separator
)

Hopefully this is what you are looking for.

Regards,
Tom

[TomMartens]

Hey @Anonymous ,

you can achieve what you are looking for by creating a measure like so:

List of New Customer = 
var theseCustomers = VALUES('Fact Sale'[Customer Key])
var CurrentMinDate = MIN('Dimension Date'[DATE])
var theseCustomerBeforeMinDate = 
    CALCULATETABLE(VALUES('Fact Sale'[Customer Key]),FILTER(ALL('Dimension Date'[Date]),'Dimension Date'[RunningMonthIndex] < CurrentMinDate),ALL('Dimension Date'))
return 
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, 'Fact Sale'[Customer Key]
, " | " 
)

• The variable theseCustomers contains the current customers
• the minimum date is determined
• then all the customers are determined that bought something before the min date
• the customers that are not in the "table" theseCustomerBeforeMinDate have to be the new customers, this is determined by using the function except
• CONCATENATEX is used to create a string where the new customers are separated by " | "

Hopefully, this is what you are looking for. If not, please create a pbix file that contains sample data, but still reflects your data model, upload the pbix to onedrive or dropbox and share the link.

Regards,

Tom

[Anonymous]

I appreciate the response, but I'm struggling with the DAX provided.  I keep getting errors around the month index.

Below is the DAX I am trying to use after changing some of the formula to match my tables, column...

List of New Customer = 
var theseCustomers = VALUES(Invoices[Customer])
var CurrentMinDate = MIN(CalendarDate[Date])
var theseCustomerBeforeMinDate = 
    CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[MonthIndex] < CurrentMinDate),ALL(CalendarDate))
return 
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, Invoices[Customer]
, " | " 
)

The error I am seeing now is around the "monthindex"...

All the best,

Rich

[TomMartens]

Hey,
please excuse.

Just replace the reference to the column MonthIndex with the Date column from the Calendar table.

Regards,
Tom

[Anonymous]

Hi Tom,

The DAX you provided worked as described.  I changed the separator you suggested to be a line break and it now appears like a list.

This leads to my next question though, would it be possible to use similar DAX so that I can add additional information?  Such as customer name and customer country?

I tried a new column, that concatenated the customer name and customer country on the customer master sheet, and then adding it into the DAX you suggested, but it will not allow me to do that.  

Any thoughts?

[TomMartens · Accepted solution]

Hey,

yes it could be done, but some for effort is necessary ...

EXCEPT returns a table, instead of using this as the 1st argument of concatenatex store this into a variable.

Then use something like this

CONCATENATEX(
FILTER(ALL('<customertable>')
, '<customertable>'[customer key] IN tablevariable
)
, concatenate all the column values
, new line separator
)

Hopefully this is what you are looking for.

Regards,
Tom

[Anonymous]

Hi Tom,

First off, I appreciate your help with this.  I'm still relatively new to PBI and I am trying to learn as much as I can about it, but clearly this last question is above my skill-level.  The first solution you provided works sufficiently for now.

My attempt at your latest suggestion is below.  I do not know DAX well enough to troubleshoot the problem with it yet.  If you could help, that would be great, but understood if not.

All the best,

Rich

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try to change the comma to "IN" or other operators.

The syntax of Filter is like below. We need to write a Boolean expression for <filter>. You could reference the document to learn more.

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Everyone,

I send good wishes in this time of Covid-19. 

My apologies for the delay in responding on this tread, but I still face issues.  I believe I have used the IN function properly, but I am still getting an error.  Please see below and assist if possible.

Is it problem with the # of items I am trying to concatentate?

Thanks

Rich

[amitchandak]

I corrected the formula . But Please find a new different approch at the end 

//date getting compared with Month, Moved to Date
List of New Customer = 
var theseCustomers = VALUES(Invoices[Customer])
var CurrentMinDate = MIN(CalendarDate[Date])
var theseCustomerBeforeMinDate = 
    CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[Date] < CurrentMinDate),ALL(CalendarDate))
return 
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, Invoices[Customer]
, " | " 
)

//calculated month index
List of New Customer = 
var theseCustomers = VALUES(Invoices[Customer])
var CurrentMinDate = mixx(filter(CalendarDate,CalendarDate[Date]<=MIN(CalendarDate[Date])),CalendarDate[MonthIndex])
var theseCustomerBeforeMinDate = 
    CALCULATETABLE(VALUES(Invoices[Customer]),FILTER(ALL(CalendarDate[Date]),CalendarDate[MonthIndex] < CurrentMinDate),ALL(CalendarDate))
return 
CONCATENATEX(
EXCEPT(theseCustomers,theseCustomerBeforeMinDate )
, Invoices[Customer]
, " | " 
)

New column approch. In this we find the min date for that customer and give a value one or customer id. Now you can use this field to get new customer. 

//new column
Is first Purchase = if(Invoices[Invoice date] = minx(filter(Invoices,Invoices[Customer]=earlier(Invoices[Customer])),Invoices[Invoice date]),1,0)

[VasTg]

@Anonymous 

Rich, Happy to help. Could you post some sample data instead of screenshot?

What happened when you tried with matrix or table visual?

[Anonymous]

I'm not sure the best way to share the data.  I'll have to come up with a way to hide some of the customer's details...

In the meantime, to answer your other question...  when I try a matrix I seem to get all of the orders in the month.  I know with certainty that some of our customers order every month, usually multiple times, but when I click on any column in the chart to cross filter the list and see the "new customers" that month, the regular customers will appear along with the new customers.  Further frustrating me, is that the same customer will show up multiple times in the same month.

My count by month of first time orders is done using a DAX calculation, I'm not sure if that matters.  Seems to work in the chart but not in a table.

Thanks