Forum Discussion

lmh100's avatar
lmh100
New Member
8 years ago
Solved

Count distinct values by date

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 requ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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] )
)

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago

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.