Lost Customer DAX Measures

[Fitin1rb]

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

[Zubair_Muhammad]

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] ) )
    )

[Fitin1rb]

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]

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]

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.