Calculated Column - New / Lost / Existing Customer

[QuantusPools]

VAR CurrentYear = YEAR(TODAY())
VAR LastYear = CurrentYear - 1
VAR CurrentYearSales = CALCULATE(SUM(Sales[Subtotal]), FILTER(Sales, YEAR(Sales[Completion Date]) = CurrentYear))
VAR LastYearSales = CALCULATE(SUM(Sales[Subtotal]), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear))
RETURN
    IF(
        CurrentYearSales > 0 && LastYearSales > 0,
        "Current",
        IF(
            CurrentYearSales > 0 && LastYearSales = 0,
            "New",
            IF(
                CurrentYearSales = 0 && LastYearSales > 0,
                "Lost",
                ""
            )
        )
    )

Customer Name	Completion Date	Subtotal	Customer Status
Customer 1	1/13/2022	$55.00	Current
Customer 1	5/5/2023	$780.00	Current
Customer 1	5/9/2022	$654.60	Current
Customer 2	5/12/2022	$247.15	Lost
Customer 2	5/20/2022	$204.72	Lost
Customer 2	5/26/2022	$0.00	Lost
Customer 3	5/23/2023	$153.00	New
Customer 3	5/26/2023	$153.00	New
Customer 3	6/13/2023	$0.00	New

I'm wanting to use a calculated column so I can use it as a filter.

I want the output to be

Current customer - Sales last year AND this year

New Customer - Sales ONLY this year

Lost Customer - Sales ONLY last year

I added the "Customer Status" column to my sample data as that is what I want my output to be.  The current posted formula only outputs "Current" customer.  Not sure why that is happening.  Any help?

[QuantusPools · Accepted solution]

I figured it out (well chatGPT did 😂).  Just had to ask a more detailed question.

Customer Status =
VAR CurrentYear = YEAR(TODAY())
VAR LastYear = CurrentYear - 1
VAR CustomerID = [Customer ID]  // Replace [Customer ID] with your actual customer identifier column name

RETURN
    IF(
        CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = CurrentYear), Sales[Customer ID] = CustomerID) > 0 &&
        CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear), Sales[Customer ID] = CustomerID) > 0,
        "Current",
        IF(
            CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = CurrentYear), Sales[Customer ID] = CustomerID) > 0 &&
            CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear), Sales[Customer ID] = CustomerID) = 0,
            "New",
            IF(
                CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = CurrentYear), Sales[Customer ID] = CustomerID) = 0 &&
                CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear), Sales[Customer ID] = CustomerID) > 0,
                "Lost",
                ""
            )
        )
    )

[Ahmedx]

ased on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

New-Lost - Existing Customer.pbix