0

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

and here is the expected result.

enter image description here

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.

Mathieu Ricour
  • 368
  • 1
  • 4
  • 25
  • Calculate New, Returning, Lost, and Recovered Customers https://www.sqlbi.com/blog/marco/2014/06/26/calculate-new-returning-lost-and-recovered-customers-in-dax/ – TDP Feb 26 '18 at 17:25

1 Answers1

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])
Alexis Olson
  • 38,724
  • 7
  • 42
  • 64