Count Distinct unique values by Month

[Anonymous]

Hello

I am trying to create a calculated column in a table that displays total number of distinct unique values per month. For instance, in the below table, there are 3 unique values - A1, B2 and B3 in the period of Jan 2017. So I need a count of 3 to be displayed in my count column.

Date	Unique value	Count required
31/01/2017	A1	3
31/01/2017	B2	3
31/01/2017	A1	3
31/01/2017	B3	3
31/01/2017	A1	3
28/02/2017	A1	2
28/02/2017	A1	2
28/02/2017	B2	2
28/02/2017	B2	2
31/03/2017	A1	4
31/03/2017	B2	4
31/03/2017	B3	4
31/03/2017	C3	4

Is it possible to calculate the 'Count required' column through DAX?

Any help will be appreciated.

Thanks
Shreyas

[v-danhe-msft · Accepted solution]

Hi @Anonymous ,

Based on my test, you could refer to below steps:

Create a Month column:

Month = MONTH('Table2'[Date])

Create below measure:

Measure = CALCULATE(DISTINCTCOUNT(Table2[Unique value]),FILTER(ALL('Table2'),'Table2'[Month]=MAX('Table2'[Month])))

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[Anonymous]

Thanks @v-danhe-msft