Distinct Count Measure in Table by Date repeating counts

[bhamp22]

Hello,

I am trying to make a distinct count table that shows how many new grant proposals were awarded in a given month. The issue that some grants are listed multiple times when a modification is awarded. However, we do not count this as a new award and it should not be counted twice. I used this measure to determine the distinct number of awards:

Award Distinct Count = DISTINCTCOUNT('Table'[Award Number])

When I place this in a table by month, the numbers are incorrect. Doing some digging, I suspect it is because the measure is only distinctly counting the awards in that month and not the whole data set.

Basically, I want the distinct count to ignore items that were counted in a previous month (such as if Award 1 was counted in Jan-20, it should not be counted again in Jan-21). How can I make the distinct count do this?

Here is a sample table I made:

This is the table visualization you get by my measure:

The Values should be:

Jan-2020: 3

Feb-2020: 0

Jan-2021: 1

Let me know if any further information is needed.

[AlexisOlson · Accepted solution]

It seems that you want a count of 'new' awards.

Try this:

Award Distinct Count =
VAR CurrDate = MIN ( 'Table'[Date] )
VAR CurrAwards = VALUES ( 'Table'[Award Number] )
VAR PrevAwards =
    CALCULATETABLE (
        VALUES ( 'Table'[Award Number] ),
        ALL ( 'Table' ),
        'Table'[Date] < CurrDate
    )
RETURN
    COUNTROWS ( EXCEPT ( CurrAwards, PrevAwards ) )

This takes the prizes in the current month, deletes the previously existing ones and counts the remaining ones.

[bhamp22]

That worked! Thanks for the help!

[AlexisOlson]

I don't quite follow your example. Why should Jan-2020 be 3?

[bhamp22]

My bad. It should only be two. Sorry for the confusion.