Calculated Column - New / Lost / Existing Customer

Question

&nbsp;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 &gt; 0 &amp;&amp; LastYearSales &gt; 0,
        "Current",
        IF(
            CurrentYearSales &gt; 0 &amp;&amp; LastYearSales = 0,
            "New",
            IF(
                CurrentYearSales = 0 &amp;&amp; LastYearSales &gt; 0,
                "Lost",
                ""
            )
        )
    )&nbsp;&nbsp;Customer NameCompletion DateSubtotalCustomer StatusCustomer 11/13/2022$55.00CurrentCustomer 15/5/2023$780.00CurrentCustomer 15/9/2022$654.60CurrentCustomer 25/12/2022$247.15LostCustomer 25/20/2022$204.72LostCustomer 25/26/2022$0.00LostCustomer 35/23/2023$153.00NewCustomer 35/26/2023$153.00NewCustomer 36/13/2023$0.00New&nbsp;I'm wanting to use a calculated column so I can use it as a filter.&nbsp;I want the output to be&nbsp;Current customer - Sales last year AND this yearNew Customer - Sales ONLY this yearLost Customer - Sales ONLY last year&nbsp;&nbsp;I added the "Customer Status" column to my sample data as that is what I want my output to be.&nbsp; The current posted formula only outputs "Current" customer.&nbsp; Not sure why that is happening.&nbsp; Any help?

quantuspools · Accepted Answer

I figured it out (well chatGPT did 😂).&nbsp; 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) &gt; 0 &amp;&amp;
        CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear), Sales[Customer ID] = CustomerID) &gt; 0,
        "Current",
        IF(
            CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = CurrentYear), Sales[Customer ID] = CustomerID) &gt; 0 &amp;&amp;
            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 &amp;&amp;
                CALCULATE(COUNTROWS(Sales), FILTER(Sales, YEAR(Sales[Completion Date]) = LastYear), Sales[Customer ID] = CustomerID) &gt; 0,
                "Lost",
                ""
            )
        )
    )&nbsp;&nbsp;

Forum Discussion

Related content

DAX help calculating Repeat Customers

Customer Scorecard

Editing/Modifying an Existing Custom Visual

Moving Average using Existing Measure Calculation

Custom Visual