Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Fitin1rb
Helper III
Helper III

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

4 REPLIES 4
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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)

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.