Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello -
I am trying to calculate a New Customer Analysis. I have 3 years worth of data and I am trying to determine which customers are new in 2017 (no transactions in 2015-2016).
I have a Customers Table (joined on Cust_ID) and a Date Table (Joined on Transaction Date) that are both joined to my Transaction table and below are a few sample transactions in my data.
Cust_ID | Transaction_Date | Amount |
1 | 1/1/2015 | 100.00 |
2 | 5/1/2015 | 100.00 |
3 | 5/1/2016 | 100.00 |
4 | 10/1/2016 | 100.00 |
1 | 1/1/2017 | 100.00 |
4 | 5/1/2017 | 100.00 |
5 | 7/1/2017 | 100.00 |
6 | 8/1/2017 | 100.00 |
7 | 9/1/2017 | 100.00 |
So in this example I am expecting to see 2017 New Customers = 3 for a total Amount of $300.
Is there a measure that I can use in Order to pull this information?
Thanks
Ryan
Solved! Go to Solution.
@Zubair_Muhammad the work around that I am using is to set a Page Filter for the Cust_ID from the Calculated Table and include all Cust_ID <> blank and this seems to solve my issue for the time being.
@Zubair_Muhammad the work around that I am using is to set a Page Filter for the Cust_ID from the Calculated Table and include all Cust_ID <> blank and this seems to solve my issue for the time being.
So you also got a Blank Customer ID when you created the NEW Customers (Calculated Table)?
Wish you all the best with your project
@Zubair_Muhammad I did, it was for everything that did not fall into the 2017 New Customers. It seems to be working right now, so we shall see. Thanks for your help!
@Zubair_Muhammad thanks for the reply. Creating a table worked to get the #'s I was looking for, however, when I try to join it to my transaction table, so that I can see what customer name, industries, city/state etc our new business came from in 2017, it has every single value the same across the board unless I keep Cust_ID in there. Is there anyway to change that?
As for the other suggestions, my transaction data set is well over 1 million rows of data, would creating a calculated column like that on that large of a data set cause any performance issues?
Thanks
Ryan
As a Measure, try this
New or Old_MEASURE = IF ( YEAR ( SELECTEDVALUE ( Transactions_Table[Transaction_Date] ) ) = 2017, IF ( CALCULATE ( COUNTROWS ( Transactions_Table ), FILTER ( ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ), YEAR ( Transactions_Table[Transaction_Date] ) < 2017 ) ) >= 1, "Old", "New" ) )
Hi @Fitin1rb
Were you able to create a relationship between CALCULATED TABLE (of new customers) and Customers Information Table?
If relationship is established, it should be easy to view more information about these new customers
Hi @Zubair_Muhammad I have created a relationship between the Calculated Table (New Customers) and the Customer Info Dimensions table (which is joined to my Transaction Table by Cust_ID) on the Cust_ID. This is the type of results that I am seeing.
Could you share the File?
A small question?
The sales field in the above Table Visual... is it coming from Transactions Table or the Calculated Table?
@Zubair_Muhammad Unfortunately I cannot share the file. I am using the SALES amount from the calculated table of new customers.
Another simple technique or Workaround could be to add calculated column in the "Calculated Table (of New Customers)" to add Relevant information such as Industry or Segment using LOOKUPVALUE or RELATED or RELATED TABLE functions in DAX
The approach I'd take is to look to use the MIN function and return the earliest date from the transaction date, and use that as a way to filter your data table. I'm not sure whether you could use this all in measures or whether you'd have to make a calculated column in your customer table
Hi @Fitin1rb
One way of doing it
Go to Modelling tab>>>select the "New Table" button
New Customers Table = VAR customersIn2017 = CALCULATETABLE ( VALUES ( Transactions_Table[Cust_ID] ), YEAR ( Transactions_Table[Transaction_Date] ) = 2017 ) VAR customersbefore2017 = CALCULATETABLE ( VALUES ( Transactions_Table[Cust_ID] ), YEAR ( Transactions_Table[Transaction_Date] ) <> 2017 ) RETURN SUMMARIZE ( EXCEPT ( customersIn2017, customersbefore2017 ), Transactions_Table[Cust_ID], "Sales", CALCULATE ( SUM ( Transactions_Table[Amount] ), YEAR ( Transactions_Table[Transaction_Date] ) = 2017 ) )
Hi @Fitin1rb
Another way of doing it is to Add a Calculated Column in the Transactions Table identifying customers as Old or New
New or Old = IF ( YEAR ( Transactions_Table[Transaction_Date] ) = 2017, IF ( CALCULATE ( COUNTROWS ( Transactions_Table ), FILTER ( ALLEXCEPT ( Transactions_Table, Transactions_Table[Cust_ID] ), YEAR ( Transactions_Table[Transaction_Date] ) < 2017 ) ) >= 1, "Old", "New" ) )