New and Lost Customers per month (help!)

[Ellis_Woods]

Hi all,

I am new to PowerBI and currently learning (so please use layman terms if you don't mind :)).

I have an excel spreadsheet of Sales data with daily transactions being recorded onto it. I have customer names, revenue (line value), dates, etc (see below) and need to turn this into a line chart with customers lost and gained per month.

A lost customer is one that has not ordered in the prior12 months and a gained customer is one that has ordered despite not ordering for the prior 12 months (so encompassing new and long-term returning customers).

Does anyone have an idea how to do this? really struggling..

🙂

[ERD · Accepted solution]

Hi @Ellis_Woods , these are examples of measures you might need:

1. I suppose you have a Date table

2. Measures:

Date New Customer = 
CALCULATE (       
    MIN ( Sales[Order Date] ),  -- The date of the first sale is the MIN of Order Date
    REMOVEFILTERS ( 'Date' )    -- at any time in the past
)

Date Lost Customer = 
CALCULATE (                                   -- The last sale occurs 12 months after 
    EOMONTH ( MAX ( Sales[Order Date] ), 12 ), -- the last transaction (end of month)
    REMOVEFILTERS ( 'Date' )                  -- at any time
)

Date Temporary Lost Customer = 
VAR MaxDate =                   -- The date of the last sale is the MAX of the Order Date
    MAX ( Sales[Order Date] )   -- in the current period (set by the calling measure)
VAR Result =
    IF (
        NOT ISBLANK ( MaxDate ),         
        EOMONTH ( MaxDate, 12 )  -- 12 months later (end of month)
    )
RETURN
    Result

# New Customers = 
VAR CustomersWithNewDate =
    CALCULATETABLE (                        -- Prepares a table that 
        ADDCOLUMNS (                        -- for each customer contains
            VALUES ( Sales[CustomerKey] ),  -- the date of their first purchase
            "@NewCustomerDate", [Date New Customer]
        ),
        ALLSELECTED ( 'Date' )              
    )
VAR CustomersWithLineage =                  -- Here we change the data lineage
    TREATAS (                               -- of the CustomersWithNewDate variable
        CustomersWithNewDate,               -- so that it filters the 
        Sales[CustomerKey],                 -- Customer Key and the
        'Date'[Date]                        -- Date columns in different tables
    )
VAR Result =                                
    CALCULATE (                            
        DISTINCTCOUNT ( Sales[CustomerKey] ), -- Counts the number of customers only
        KEEPFILTERS ( CustomersWithLineage )  -- if they appear in their @NewCustomerDate
    )
RETURN
    Result

# Lost Customers = 
VAR LastDateLost =
    CALCULATE (
        MAX ( 'Date'[Date] ),
        ALLSELECTED ( 'Date' )
    )
VAR CustomersWithLostDate =
    CALCULATETABLE (                        -- Prepares a table that 
        ADDCOLUMNS (                        -- for each customer contains
            VALUES ( Sales[CustomerKey] ),  -- the date when they are considered lost
            "@LostCustomerDate", [Date Lost Customer]
        ),
        'Date'[Date] <= LastDateLost     --Regardless of local filters on Date
    )
VAR LostCustomers =
    FILTER (                             
        CustomersWithLostDate,          -- Filters the customers
        [@LostCustomerDate]             -- whose Lost Customer Date
            IN VALUES ( 'Date'[Date] )  -- falls within the current time period
    )
VAR Result = COUNTROWS ( LostCustomers )        
RETURN
    Result

# Recovered Customers = 
VAR MinDate = MIN ( 'Date'[Date] )
VAR CustomersWithLostDateComplete =
    CALCULATETABLE (                       -- Prepares a table that 
        ADDCOLUMNS (                       -- for each customer contains
            VALUES ( Sales[CustomerKey] ), -- the temporarily-lost date
            "@TemporarilyLostCustomerDate", CALCULATE (
                [Date Temporary Lost Customer],
                'Date'[Date] < MinDate 
            )
        ),
        ALLSELECTED ( 'Date' )            
    )
VAR CustomersWithLostDate =
    FILTER (                               -- Removes the customer without a 
        CustomersWithLostDateComplete,     -- temporarily-lost date
        NOT ISBLANK ( [@TemporarilyLostCustomerDate] )
    )
VAR ActiveCustomers =
    ADDCOLUMNS (                           -- Gets the first order date of
        VALUES ( Sales[CustomerKey] ),     -- customers in the current selection
        "@MinOrderDate", CALCULATE ( MIN ( Sales[Order Date] ) )
    )
VAR RecoveredCustomers =                  
    FILTER (
        NATURALINNERJOIN (                 -- Filters the recovered customers 
            ActiveCustomers,               -- by combining active customers
            CustomersWithLostDate          -- and temporarily-lost customers
        ),                                 -- and then comparing dates
        [@MinOrderDate] > [@TemporarilyLostCustomerDate]
    )
VAR Result = COUNTROWS ( RecoveredCustomers )
RETURN
    Result

# New and Recovered Customers = [# New Customers] + [# Recovered Customers]

Result:

I believe this article will answer lots of questions on this topic: 

New and returning customers