Count distinct values that adds each month?

[Anmolgan]

I want to count distinct values that gets added each month

NAMES	Date
A1111	30-May-19
A2234	22-Jul-19
A2141	28-Jun-19
A2234	23-Jun-19
A2431	29-Jun-19
A421	13-May-19
A24312	26-Jun-19
A2151	4-Jul-19

Above is just a sample, considering above as distinct I only need to count values that are added as a distinct.

[az38]

Hi @Anmolgan 

please, demonstrate to us your desired output to help you

thx

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anmolgan]

@az38 I need to find what are the number of Names that gets added each month, now if I create a table visual in powerbi using month and names as Distinct Count, it will show me overall customers that keeps on increasing for example:

AUG	100
SEP	150
OCT	200
NOV	201

if you look above, count keeps increasing now I need to find added customers so i need the below output:

month	count distinct of names	added names each month
AUG	100	100
SEP	150	50
OCT	200	50
NOV	201	1

I hope that I was able to explain you?

[az38 · Accepted solution]

Hi @Anmolgan 

you can create a calculated table

Table 2 = SUMMARIZE('Table';'Table'[NAMES];"First appear";MIN('Table'[Date]))

then in visual set date hierarchy items Year and Month to display

do not hesitate to give a kudo to useful posts and mark solutions as solution

[az38]

@Anmolgan 

share pbix if you can 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anmolgan]

I had shared the pbix with you in your private chat

[Anmolgan]

@az38 

Something is wrong in my case, I have used your formula, if you look at the Actual Count is the count that keeps on increasing each month, and the Count GRS Name is what I have build according to your DAX measure, the subtraction should be correctly performing if I am correct, and I have build Many 2 Many relationship between the custom table and the master tables, do let me know if you need the pbix file.

[Anmolgan]

@az38  I need to find the subtraction of previous month count with the current month count for example:

month	count distinct of names
AUG	100
SEP	150

the above I have but I want to know how many of the count added on September that is 150-100=50, this I want to do for all the months of any year.

[az38]

@Anmolgan 

in my statement you will see how many new customers appear in each month you need. as i see is exactly your task

do not hesitate to give a kudo to useful posts and mark solutions as solution