Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |