New Customer Sales

[Eric_Guli]

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.

[Thyago_Rezende]

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,

[Eric_Guli]

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.

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.

[Thyago_Rezende]

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

[Eric_Guli]

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

Thanks!

[Ashish_Mathur]

Hi,

Please share some data and show the expected result.

[Eric_Guli]

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

SXW	101050860	All American Containers	7/31/2014
SXCN	201062105	All American Containers	12/23/2015
SXW	123000298	Allied Wine Corp.	12/10/2014
SXCN	201060659	Allied Wine Corp.	9/29/2013
SXW	101051459	Altitude Spirits	2/18/2015
SXE	ALTI01	Altitude Spirits	6/15/2017
SXW	123000353	Auston Design Group	1/16/2015
SXE	AUST02	AUSTON DESIGN GROUP	12/3/2013
SXW	101022930	Axe And The Oak Distillery	7/31/2014
SXE	AXEO01	Axe And The Oak Distillery	9/7/2016
SXE	BACB01	BACK BAY BREWING	5/9/2017
SXCN	201062143	Back Bay Brewing	6/5/2017
SXW	101052708	Bay Etching &  Imprinting	2/7/2017
SXCN	201062158	Bay Etching &  Imprinting	1/4/2018
SXW	101016842	Bayhawk Ales, Inc.	7/31/2014
SXW	101052101	Bayhawk Ales, Inc.	8/15/2016
SXW	101011229	Berlin Packaging	7/31/2014
SXE	BERL01	BERLIN PACKAGING	5/23/2014

[Ashish_Mathur]

Hi,

Please also share the exact result you are expecting.

[Eric_Guli]

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

Legal Entity	Customer account	Name	Created date	Min Created Date
SXW	123000298	Allied Wine Corp.	12/10/2014	9/29/2013
SXCN	201060659	Allied Wine Corp.	9/29/2013	9/29/2013
SXW	101053074	ALLTECH	2/16/2018	12/3/2013
SXE	ALLT01	ALLTECH	12/3/2013	12/3/2013
SXW	123000374	Aloha Medicinals Inc.	1/26/2015	7/31/2014
SXW	101019758	Aloha Medicinals, Inc	7/31/2014	7/31/2014
SXW	101051459	Altitude Spirits	2/18/2015	2/18/2015
SXE	ALTI01	Altitude Spirits	6/15/2017	2/18/2015

[Ashish_Mathur]

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.

[Eric_Guli]

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.

[Ashish_Mathur]

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.

[Eric_Guli]

Thank you for your continued support.

Here is what is happening today.

Legal Entity	Customer Name	Created Date	Invoice Date	Sales Current	Sales New
sxcn	Water Con	1/30/2017	10/17/2017		$27,210
sxcn	Water Con	1/30/2017	5/31/2017		$116,447
sxcn	Water Con	1/30/2017	5/25/2017		$241,631
sxcn	Water Con	1/30/2017	5/5/2017		$81,631
sxcn	Water Con	1/30/2017	4/25/2017		$163,262
sxcn	Water Con	1/30/2017	4/25/2017		$54,492
sxcn	Water Con	1/30/2017	4/24/2017		$27,210
sxw	Water Con	6/8/2016	1/31/2017	$25,261
sxw	Water Con	6/8/2016	11/17/2016		$3,360
sxw	Water Con	6/8/2016	8/31/2016		$25,407
sxw	Water Con	6/8/2016	8/31/2016		$2,856

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

Legal Entity	Customer Name	Created Date	Invoice Date	Sales Current	Sales New
sxcn	Water Con	1/30/2017	10/17/2017	$27,210
sxcn	Water Con	1/30/2017	5/31/2017	$116,447
sxcn	Water Con	1/30/2017	5/25/2017	$241,631
sxcn	Water Con	1/30/2017	5/5/2017	$81,631
sxcn	Water Con	1/30/2017	4/25/2017	$163,262
sxcn	Water Con	1/30/2017	4/25/2017	$54,492
sxcn	Water Con	1/30/2017	4/24/2017	$27,210
sxw	Water Con	6/8/2016	1/31/2017	$25,261
sxw	Water Con	6/8/2016	11/17/2016	$3,360
sxw	Water Con	6/8/2016	8/31/2016	$25,407
sxw	Water Con	6/8/2016	8/31/2016	$2,856

[Ashish_Mathur]

I just cannot understand.  Someone else will help you.

[Eric_Guli]

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!