Churn rate - count if same number DAX

[aletho]

Hey all

I am trying to make a visualization in Power bi showing the churn rate. 

What I have is some data that look likes the date in the table below. I want to see if the customer has made it to subscription 1, 2 or 3 over a period of time. If a could do that I can create a column chart as below. 

Is it possible to create a DAX measure that counts the number of unique "user id", as I have tried to illustrate in the table.

Thanks! 

Date	User id	COUNT
26/08/2018	1	1
27/08/2018	2	1
28/08/2018	3	1
29/08/2018	4	1
30/08/2018	5	1
31/08/2018	6	1
01/09/2018	2	3
02/09/2018	8	1
03/09/2018	9	1
04/09/2018	10	1
05/09/2018	11	2
06/09/2018	12	1
07/09/2018	8	2
08/09/2018	2	3
09/09/2018	13	1
10/09/2018	11	2
11/09/2018	8	2

[Greg_Deckler]

I believe:

Measure = DISTINCTCOUNT('Table'[User id])

[aletho]

Thank you for the reply. The distinctcount shows me the user id that appears more than once, which is good to show me the number of subscribers who stay more than one subscriptions period. I would like to be able to see the once that stay for 1, 2 or 3 periods. That's that make sense? 🙂 

This is what I can create when using distinctcount - thanks again and any other suggestions are more than welcome 🙂 

[Greg_Deckler]

Use Count in your Axis?

[aletho]

I don't have the row "count", that's the one I would like to create 🙂 

[Greg_Deckler]

If you do not have a column called COUNT, although you show it in your table, use this:

COUNT = COUNTROWS(FILTER(ALL('Table'),[User id]=EARLIER([User id])))

[aletho]

Sorry I made it to illustrate what I was after.

I tried your measure but cannot get it to work properly. 

I have made a link to my date on google drive:

https://drive.google.com/open?id=1r6nZbSvJZsv49efRqsYzvW9OQ-RuDLoK

packageId is like "user id" and what I would like to create the "count column" from. 

Understand if it is to much asking you to look at it again. 

Still thanks for your time 🙂 

[Greg_Deckler · Accepted solution]

Not sure why you couldn't get that to work but attached is PBIX of solution. You want Page 7.