Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Lost Customer DAX Measures

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_IDTransaction_DateAmount
11/1/2015  100.00
25/1/2015  100.00
35/1/2016  100.00
410/1/2016  100.00
11/1/2017  100.00
45/1/2017  100.00
57/1/2017  100.00
68/1/2017  100.00
79/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

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi Anonymous

     

    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] ) )
    )
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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

      • Greg_Deckler's avatar
        Greg_Deckler
        Community Champion
        8 years ago

        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)
  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    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.