cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Count Distinct unique values by Month

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

Hi @Shreyas1988 ,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Microsoft

Hi @Shreyas1988 ,

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:

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Thanks @v-danhe-msft

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors