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.
You can try to use TREATAS function. It provides virtual relationship between tables.
For more information about it:
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.
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.
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||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|
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||123000374||Aloha Medicinals Inc.||1/26/2015||7/31/2014|
|SXW||101019758||Aloha Medicinals, Inc||7/31/2014||7/31/2014|
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.
Don't miss the Power BI Dev Camp this week!
Check out a full recap of the month!
Check out the winners of the recent 'Can You Solve These?' community challenge!
Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.