Finding returning customers, year on year

[TheMilkman]

HI everyone,

I have the following sample dataset, which contains customer ids and the year they have registered for an event, along with their registrationID, which is unique. The values in the Year column range between 2020 and 2024.

customerid || year  || registrationid

LM-0001     || 2020 || ABC
LM-0002     || 2021 || ABCD
LM-0001     || 2021 || ABCDE
LM-0003     || 2022 || ABCDEF
LM-0002     || 2024 || ABCDEFG

What i want to achieve, is adding a calculated column which states whether they are a new customer or returning. One caveat is that the year 2020, all customers who registered, would have been "New Customers", as it was the first year, however if they then registered in 2023, they then would be classed as a returning customer.

The outcome would be to achieve the following table:

customerid || Year  || registrationID   || customerStatus

LM-0001     || 2020 || ABC                  || New
LM-0002     || 2021 || ABCD               || New
LM-0001     || 2021 || ABCDE             || Returning
LM-0003     || 2022 || ABCDEF           || New
LM-0002     || 2024 || ABCDEFG        || Returning

customer LM-0001 is a new customer being a registrant in 2020 however then returned in 2021, so they are deemed a returning customer.  With LM-0002, they were a new customer in 2021 however they registered again in 2024, thus being a returning customer.
if a customer registered in 2022 and have not registered since, they would be listed as a new customer until they register again, in which their status for that relative year will be Returning.

I hope that makes sense, any help and guidance will be much appreciated.

Kind regards,

Calvin

[Sahir_Maharaj · Accepted solution]

Hello @TheMilkman,

Can you please try this:

customerStatus = 
IF(
    COUNTROWS(
        FILTER(
            ALL(Table),
            Table[customerid] = EARLIER(Table[customerid]) && Table[year] < EARLIER(Table[year])
        )
    ) > 0,
    "Returning",
    "New"
)

Should you require my further assistance, please do not hesitate to reach out to me.

[Dangar332 · Accepted solution]

hi, @TheMilkman 

Try below code 

customerstatus =

var a= CALCULATE(DISTINCTCOUNT(customer[year]),ALLEXCEPT(customer,customer[customerid]))

var b = CALCULATE(MIN(customer[year]),ALLEXCEPT(customer,customer[customerid]))

var c = IF(a=1 || customer[year]=b,"new","returning")

return c

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly. Appreciate your kudos

[Sahir_Maharaj · Accepted solution]

Hello @TheMilkman,

Can you please try this:

customerStatus = 
IF(
    COUNTROWS(
        FILTER(
            ALL(Table),
            Table[customerid] = EARLIER(Table[customerid]) && Table[year] < EARLIER(Table[year])
        )
    ) > 0,
    "Returning",
    "New"
)

Should you require my further assistance, please do not hesitate to reach out to me.