cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

New Customer Sales

Hello Power BI Community,

 

I am looking for a solution on capturing New Customer Sales but I have the following issue that I haven't been able to overcome. We have three (3) Legal Entities in our company and it's possible to have the same customer setup in each unit. The customer name may be the exact spelling but a new account number is created in each unit.

 

I have created calculated columns to identify the duplicate customer names and then tried to use either MIN or FIRSTDATE to capture the Created Date. I have even tried to use first invoice date but it always brings back the MIN date of the Legal Entity the customer was created in, not the MIN of the multiple accounts. I am racking my head against a wall here and thought I would reach out the you to see if there is a solution.

 

The second issue which may be solved by the first is when a customer is setup in the prior year but a sale doesn’t take place until the following year this should be classified as a new sale in the current year but due to the created date being in the prior year it shows as an existing customer.

 

Thank you for your help.

14 REPLIES 14
Highlighted

Thank you very much!! That worked for getting the Min Created date from the customer table. Now I am having trouble getting the DAX code to work with the new Min Created Date. I am using a set of formulas I found on this support site that gives me current and new customer sales. The reason I posted my first post here is due to the fact that if a customer exists in two legal entities it will show as a new customer based on the code below (Sales New Customer). If I try to substitute the new Min Created Date measure I get all kinds of weird results.

 

Adding the two measures to my customer table I can now get the min created date for any customer regardless of legal entity.

Create Date Min =
MIN(DwCustMaster[CreatedDateTime])

 

Min Create Date =
CALCULATE([Create Date Min],ALL(DwCustMaster[LegalEntity]))

 

 

Sales Current Customers =
CALCULATE (
SUM ( DWSalesHistory[Sales] ),
FILTER (
DWSalesHistory,
YEAR ( DWSalesHistory[InvoiceDate] )
> RELATED ( DwCustMaster[Created Year])
)
)

 

and then

 

Sales New Customers =
CALCULATE (
SUM ( DWSalesHistory[Sales] ),
FILTER (
DWSalesHistory,
YEAR ( DWSalesHistory[InvoiceDate] )
= RELATED ( DwCustMaster[Created Year])
)
)

 

Any help would be great on coding this correctly with the new Min Created Date measure.

 

Thank you.

Highlighted

Hi,

 

You are welcome.  Do not just share the formula - i will not be able to help you.  Please descibe the business scenario, share some data and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Thank you for your continued support.

 

Here is what is happening today.

Legal EntityCustomer NameCreated DateInvoice DateSales CurrentSales New
sxcnWater Con1/30/201710/17/2017 $27,210
sxcnWater Con1/30/20175/31/2017 $116,447
sxcnWater Con1/30/20175/25/2017 $241,631
sxcnWater Con1/30/20175/5/2017 $81,631
sxcnWater Con1/30/20174/25/2017 $163,262
sxcnWater Con1/30/20174/25/2017 $54,492
sxcnWater Con1/30/20174/24/2017 $27,210
sxwWater Con6/8/20161/31/2017$25,261 
sxwWater Con6/8/201611/17/2016 $3,360
sxwWater Con6/8/20168/31/2016 $25,407
sxwWater Con6/8/20168/31/2016 $2,856


The desired output should be the following is we can use the MIN CREATED DATE Measure. 

Legal EntityCustomer NameCreated DateInvoice DateSales CurrentSales New
sxcnWater Con1/30/201710/17/2017$27,210 
sxcnWater Con1/30/20175/31/2017$116,447 
sxcnWater Con1/30/20175/25/2017$241,631 
sxcnWater Con1/30/20175/5/2017$81,631 
sxcnWater Con1/30/20174/25/2017$163,262 
sxcnWater Con1/30/20174/25/2017$54,492 
sxcnWater Con1/30/20174/24/2017$27,210 
sxwWater Con6/8/20161/31/2017$25,261 
sxwWater Con6/8/201611/17/2016$3,360 
sxwWater Con6/8/20168/31/2016$25,407 
sxwWater Con6/8/20168/31/2016$2,856 

 

Highlighted

I just cannot understand.  Someone else will help you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted

Thank you again. Yes, this has been the problem from the begining to write a DAX expression that can look for the Min Customer Created Date regardless of the Legal entity it may belong to. We have the same customers in multiple entities and trying to look only at who is actually a "NEW" has been the challenge. So in the example above the custoemr Water Con was set up in 2016 in SWX legal entity so they were new in 2016. In 2017 the customer was setup under SXCN legal entity and now shows as a new customer when in fact it isn't. 

 

Thanks!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors