Forum Discussion
New Customer Sales
Hi,
Please also share the exact result you are expecting.
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 |
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.
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.
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 I just cannot understand. Someone else will help you.
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!