Forum Discussion
Solved
Distinct Count Measure in Table by Date repeating counts
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 i...
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.
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.
That worked! Thanks for the help!