How to calculate the number of new customers every year in DAX

Question

I have to calculate the number of new customers for every year. Here is my table,

and here is the expected result.

The tricky part is that I don't have to only count the difference between two years, I have to know if there are customers who left from one year to the other and then skip them to my calculation. The result only include the amount of new customers. I hope I made myself clear.

Thanks.

Answer 1

You can use the COUNTX function to count how many customers in the current year were not in the previous year:

New Users = 
    VAR CurrentYear = MAX(Customers[Year])
    VAR OldUsers = CALCULATETABLE(
                       VALUES(Customers[Customer]),
                       Customers[Year] < CurrentYear)
    RETURN COUNTX(
               FILTER(Customers,
                   NOT(Customers[Customer] IN OldUsers)),
               Customers[Customer])