Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Eric_Guli
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
Thyago_Rezende
Resolver I
Resolver I

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,

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.

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

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

 

Thanks!

Hi,

 

Please share some data and show the expected result.


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

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

Hi,

 

Please also share the exact result you are expecting.


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

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

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/

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.

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/

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 

 

I just cannot understand.  Someone else will help you.


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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.