Customer had sales last year but not this year.

[QuantusPools]

=VAR CurrentYear = YEAR(MAX('Sales'[Completion Date]))
VAR LastYear = CurrentYear - 1
RETURN
IF(
CALCULATE(
COUNTROWS('Sales'),
FILTER(
'Sales',
YEAR('Sales'[Completion Date]) = LastYear
)
) > 0
&&
CALCULATE(
COUNTROWS('Sales'),
FILTER(
'Sales',
YEAR('Sales'[Completion Date]) = CurrentYear
)
) = 0,
1,
0
)

Customer Name	Completion Date
Customer 1	1/13/2022
Customer 1	7/25/2023
Customer 1	5/11/2023
Customer 1	5/25/2023
Customer 1	6/8/2023
Customer 1	5/20/2022
Customer 2	5/9/2022
Customer 2	5/16/2022
Customer 2	6/13/2022
Customer 2	7/9/2022
Customer 2	8/8/2022
Customer 2	9/6/2022
Customer 2	10/1/2022

This formula just returns 0 as if I have no lost customers.  It should be

1 = Made purchase in 2022 / no purchase 2023

otherwise = 0

Any help?

[QuantusPools]

It worked.  Thanks so much.

[gmsamborn · Accepted solution]

Hi @QuantusPools 

Try changing your first variable to:

VAR CurrentYear =
    YEAR(
        MAXX( 
            ALL( 'Sales'),
            [Completion Date] 
        )
    )