New/Lost/Returning/Loyal Customers

Hi Anonymous ,

Please check if this is what you want:

Fiscal Year = 
IF (
    [Purchase Date] <= DATE ( YEAR ( [Purchase Date] ), 12, 21 ),
    RIGHT ( YEAR ( [Purchase Date] ) - 1, 2 ) & "-"
        & RIGHT ( YEAR ( [Purchase Date] ), 2 ),
    RIGHT ( YEAR ( [Purchase Date] ), 2 ) & "-"
        & RIGHT ( YEAR ( [Purchase Date] ) + 1, 2 )
)

Status = 
VAR CurCustomer = [Customer]
VAR CurFY = [Fiscal Year]
VAR PreFY =
    VALUE ( LEFT ( CurFY, 2 ) ) - 1 & "-"
        & VALUE ( RIGHT ( CurFY, 2 ) ) - 1
VAR PrePreFY =
    VALUE ( LEFT ( PreFY, 2 ) ) - 1 & "-"
        & VALUE ( RIGHT ( PreFY, 2 ) ) - 1
VAR Count_CurFY =
    DISTINCT (
        SUMMARIZE (
            FILTER ( Sheet1, [Fiscal Year] = CurFY && [Customer] = CurCustomer ),
            [Customer]
        )
    )
VAR Count_PreFY =
    DISTINCT (
        SUMMARIZE (
            FILTER ( Sheet1, [Fiscal Year] = PreFY && [Customer] = CurCustomer ),
            [Customer]
        )
    )
VAR Count_PrePreFY =
    DISTINCT (
        SUMMARIZE (
            FILTER ( Sheet1, [Fiscal Year] <= PrePreFY && [Customer] = CurCustomer ),
            [Customer]
        )
    )
RETURN
    SWITCH (
        TRUE (),
        Count_CurFY > 0
            && Count_PreFY = 0
            && Count_PrePreFY = 0, "New",
        Count_CurFY > 0
            && Count_PreFY > 0, "Renewed",
        Count_CurFY > 0
            && Count_PreFY = 0
            && Count_PrePreFY > 0, "Re-activated"
    )

Best Regards,

Icey

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