Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello -
I have 3 years worth of data and I am looking to find our 'lost' customers in 2017 (so customers that had transactions in 2015-2016 but no transactions in 2017). Is there a DAX function that I can use to create a measure for this? Below is a small sample data set from a transaction table (that is joined to a date table and customer info table).
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 for the example above, I am expecting to see 2 LOST Customers (ID 2 and 3) and $200 'lost' for 2017.
Thank You
Ryan
Hi @Fitin1rb
Did you try the same technique we did for New Customers?
Modelling tab>>> "New Table" button
Lost 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 ( customersbefore2017, customersIn2017 ), Transactions_Table[Cust_ID], "Sales", CALCULATE ( SUM ( Transactions_Table[Amount] ) ) )
Hey @Zubair_Muhammad i did and it does work for the most part, but I have an issue (and having the same issue with the new customers table now too). I was to show % of totals that are New and Lost and be able to slice the data, the numerator remains the same (ALL New Customer Count or Lost Customer Count) no matter how I slice it. So % of total are all >100% when sliced.
So I am looking for a new/more dynamic way to do this.
Thanks
Ryan
You should be able to create a table using SUMMARIZE or CALCULATETABLE with these measures and probably get you there.
Cust_2015 = IF(CALCULATE(COUNTROWS(Customers),FILTER(Customers,YEAR(Customers[Transaction_Date])=2015)),1,0) Cust_2016 = IF(CALCULATE(COUNTROWS(Customers),FILTER(Customers,YEAR(Customers[Transaction_Date])=2016)),1,0) Cust_2017 = IF(CALCULATE(COUNTROWS(Customers),FILTER(Customers,YEAR(Customers[Transaction_Date])=2017)),1,0)
You could create a COUNTROWS measure wrapped in a CALCULATE and filter it to a particular year. So you would have measures for 2015, 2016 and 2017. You could then create a final measure that would check to make sure that the 2015 and 2016 measures were > 0 and that the 2017 was 0, then you would know that is a lost customer.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |