Pass Filtered Table to Calculate Function

[Anonymous]

I have a trending table and here is a sample data

   Date                         Key                  

1/27/2019                     1                       

1/27/2019                     2                       

2/27/2019                     1                     

2/27/2019                     2

3/27/2019                     1

3/27/2019                     1

My goal is to find the "Key" that has COUNT>1 in the latest date i.e. Key=1 should be outputed since it appeard twice in 3/1/2019

My logic is to create a calculated column

1- First filter the table to only inlcude latest date records 

2- Use Earlier function to get the ones that has more than one records in that filtered table

FindDups= SUMX(FILTER(TABLE,TABLE[DATE]=DATE(YEAR(TODAY()),MONTH(TODAY()),27))),IF([KEY]=EARLIER([KEY]),1,0))

But the function does not filter the table to only latest date records i.e. 3/27/2019

Any clue 🙂 please ?

Thank you

[AlB · Accepted solution]

Hi @Anonymous 

If there's only one key with count > 1 for the last date, you can create a measure and place it in a card visual:

Measure =
CALCULATE (
    DISTINCT ( Table1[Key] ),
    FILTER (
        Table1,
        CALCULATE ( COUNT ( Table1[Key] ) ) > 1 && Table1[Date] = MAX ( Table1[Date] )
    )
)

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers