Calculate Outstanding Submissions

[ajmonster]

Hello

First, I'll show you a mock data set:

Person	Month
John	1
Jake	1
Jill	1
Jill	2
John	2
Jill	4
Jake	2
John	3
John	5
Jake	3

I am looking for a way to sum how many missing records there are for a given month. For example, the correct measure would show me that January has 0, February has 0, March has 1, April has 2, and May has 2.

Thanks,

ajmonster

[Zubair_Muhammad]

@ajmonster

Try this MEASURE

Missing Records =
COUNTROWS ( ALL ( TableName[Person] ) ) - COUNT ( TableName[Person] )

[Zubair_Muhammad]

@ajmonster

[ajmonster]

@Zubair_Muhammad Upon clicking on a given month with missing records, are you able to display the name of those with missing records?

[Zubair_Muhammad · Accepted solution]

@ajmonster

To get the names of missing persons, you can use this MEASURE

MissingPerson =
CONCATENATEX (
    EXCEPT ( ALL ( TableName[Person] ), VALUES ( TableName[Person] ) ),
    TableName[Person],
    ", "
)

[Zubair_Muhammad]

@ajmonster