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
Resolver I
Resolver I

Re: New Customer Sales

Hi @Eric_Guli,

 

You can try to use TREATAS function. It provides virtual relationship between tables.

For more information about it:

https://www.sqlbi.com/articles/propagate-filters-using-treatas-in-dax/

 

Example:

CALCULATE(DISTINCTCOUNT(ID_CUSTOMERS_TABLE1);

   TREATAS(VALUES(ID_CUSTOMERS_TABLE2);

                   ID_CUSTOMERS);

   TREATAS(VALUES(ID_CUSTOMERS_TABLE3);

                  ID_CUSTOMERS)

)

Best regards,

Highlighted
Frequent Visitor

Re: New Customer Sales

Thank you very much for your reply. I may have been a little too excited for my first post and didn't provide enough background info.

We are running Microsoft AX 2012 for our entire company, there are three legal entities within our environment. SXE for East, SXW for West and SXCN for Canada. All the data is dumped into a data warehouse and into common tables. i.e. DWCustMaster. I have pasted in screen shot for better understanding.

Capture.PNGCapture1.PNG

 

When looking for new customers it would see the created date of 2015 as new when in fact it's a current account from 2013. The only way I see to check for duplicate customers is by checking the Name Column but the fact the account num and legal entity are different is the challenge.

 

I'll look at the TREATAS function to see if there is an option for me.

Highlighted
Resolver I
Resolver I

Re: New Customer Sales

What´s the correct rules for count? Are the Name, AccountNum, others? Is it necessary count for entity or global entities?

Highlighted
Frequent Visitor

Re: New Customer Sales

Basically I need to use the MIN Created Date based on the Customer Name regardless of the Account Number or Legal Entity.

 

Thanks!

Highlighted
Super User III
Super User III

Re: New Customer Sales

Hi,

 

Please share some data and show the expected result.


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

Re: New Customer Sales

Here is a snippet of data to give you an idea that I need to use the MIN date from Created Date column to be able to calculate new customer sales. In this instance I don't care about the legal entity or cutomer account number as it's the same customer.

 

Thanks!

 

Legal Entity    Customer account    Name                                                           Created date

SXW101050860All American Containers7/31/2014
SXCN201062105All American Containers12/23/2015
SXW123000298Allied Wine Corp.12/10/2014
SXCN201060659Allied Wine Corp.9/29/2013
SXW101051459Altitude Spirits2/18/2015
SXEALTI01Altitude Spirits6/15/2017
SXW123000353Auston Design Group1/16/2015
SXEAUST02AUSTON DESIGN GROUP12/3/2013
SXW101022930Axe And The Oak Distillery7/31/2014
SXEAXEO01Axe And The Oak Distillery9/7/2016
SXEBACB01BACK BAY BREWING5/9/2017
SXCN201062143Back Bay Brewing6/5/2017
SXW101052708Bay Etching &  Imprinting2/7/2017
SXCN201062158Bay Etching &  Imprinting1/4/2018
SXW101016842Bayhawk Ales, Inc.7/31/2014
SXW101052101Bayhawk Ales, Inc.8/15/2016
SXW101011229Berlin Packaging7/31/2014
SXEBERL01BERLIN PACKAGING5/23/2014
Highlighted
Super User III
Super User III

Re: New Customer Sales

Hi,

 

Please also share the exact result you are expecting.


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

Re: New Customer Sales

Sorry for the delay. Please see the expected results below.

Legal EntityCustomer accountNameCreated date Min Created Date
SXW123000298Allied Wine Corp.12/10/20149/29/2013
SXCN201060659Allied Wine Corp.9/29/20139/29/2013
SXW101053074ALLTECH2/16/201812/3/2013
SXEALLT01ALLTECH12/3/201312/3/2013
SXW123000374Aloha Medicinals Inc.1/26/20157/31/2014
SXW101019758Aloha Medicinals, Inc7/31/20147/31/2014
SXW101051459Altitude Spirits2/18/20152/18/2015
SXEALTI01Altitude Spirits6/15/20172/18/2015
Super User III
Super User III

Re: New Customer Sales

Hi,

 

These are the 2 measures that i wrote:

 

Create Date = MIN(Table1[Created Date])

Min Create Date = CALCULATE([Create date],ALL(Table1[Customer Account]),ALL(Table1[Legal Entity]))

 

Hope this helps.

 

Untitled.png


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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors