Count distinct values by date

[lmh100]

Hi, 

I am trying to create a calculated column in a table - based on counted distinct values that appear during the month - an example of the table is below:

Date	Unique value	Count requried
31/01/2017	A1	2
31/01/2017	B2	1
31/01/2017	A1	2
31/01/2017	B3	1
31/01/2017	B4	1
28/02/2017	A1	2
28/02/2017	A1	2
28/02/2017	B2	2
28/02/2017	B2	2
31/03/2017	A1	1
31/03/2017	B2	2
31/03/2017	B2	2
31/03/2017	C3	1

I have tried CALCULATE(DISTINCTCOUNT( UNIQUE VALUE , UNIQUE VALUE = UNIQUE VALUE, CALENDAR[DATE])

But i am just returned with the number rows in that month - rather than the count required column

Any help would be appreciated

Thanks,

Laurence

[Greg_Deckler]

If you just create a Table visualization and drop in your Date column, Unique Value column and then drop in your Date column again and switch the aggregation to Count you will achieve what you are looking for.

[Zubair_Muhammad · Accepted solution]

Hi @lmh100

Try this column

Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    FILTER (
        ALLEXCEPT ( TableName, TableName[Unique value] ),
        MONTH ( TableName[Date] ) = MONTH ( EARLIER ( TableName[Date] ) )
    )
)

or this one

Count Required =
CALCULATE (
    COUNT ( TableName[Unique value] ),
    ALLEXCEPT ( TableName, TableName[Unique value], TableName[Date].[Month] )
)