List of staff with no data in a time period

[sarah2]

Hello! I have a data set that contains names of staff members and dates. Ultimately I want to know which staff members I DON'T have data for in a certain month or time period (changed via date slicer). 

-other notes about my table in case it impacts the solution: a staff member might have multiple rows for the same date because of different variables. All staff members are on the main data table at some point, but they might not be in every month. I also need to be able to use a slicer to change the date range I'm looking at. I've seen other people recommend a date table which I can make, but I'm not sure how to use it in this context.

Here's a sample of the Main Data Table:

Main Data Table
Date	Name	Session ID	Variable
2-Jan	Amy	123	a
2-Jan	Amy	123	b
2-Jan	Ben	124	s
4-Jan	Ben	125	a
4-Jan	Amy	126	s
4-Jan	Ben	120	s
5-Jan	Ben	132	s
6-Jan	Amy	140	a
9-Jan	Amy	150	a
9-Jan	Amy	150	s
1-Feb	Jen	164	m
5-Feb	Jen	166	a
5-Feb	Amy	182	v
2-Mar	Mark	202	k
2-Mar	Mark	202	b
2-Mar	Mark	309	c

I also have a table with current staff listed (optional for the solution)

Current Staff List

Amy

Ben

Jen

Mark

The end product I'm looking for is a table that lists which name is missing on the Main Data Table. Example: if I have a slicer set for January, I would like a table that lists: Jen, Mark. If I have the slicer set for February, I would like the table to list: Ben, Mark
My understanding is that a merged table would work once but I'm not sure how to get it to work more long-term without manually doing it for each month.
Thank you!!

[sarah2 · Accepted solution]

With inspiration from this post I solved this by making a calculated table of all of the names 
TABLE= SUMMARIZE( 'Main Data Table', 'Main Data Table'[Name])

Then connected the relationship to the Main Data Table (one to many). On the report I then made a measure:
Measure= CALCULATE(DISTINCTCOUNT('Main Data Table'[Session ID])+0

With a table in the report I added the names from my new Table and this measure which resulted in what I wanted - a count of session IDs including showing zeros

[v-weiyan1-msft]

Hi @sarah2 ,

Based on the example and description you provided, please try the following steps:
1.You can create Calculated table.

Table = DISTINCT('Main Data Table'[Date])

Then try code as below to create Calculated column.

Month = 'Table'[Date].[Month]

MonthNumber = 'Table'[Date].[MonthNo]

2. Use the following code to create a measure.

Measure = 
VAR SelectedMonth =
    SELECTEDVALUE ( 'Table'[Month] )
VAR Count_ =
    COUNTX (
        FILTER ( 'Main Data Table', 'Main Data Table'[Date].[Month] = SelectedMonth ),
        'Main Data Table'[Name]
    )
RETURN
    IF ( ISBLANK ( SelectedMonth ), 1, IF ( Count_ >= 1, 0, 1 ) )

The fields in the table are as follows.

Select table visual, put the measure in the "Filters on this visual" section, and filter it by "Measure is 1".

When you select "January" in the slicer, Result is as below.

For further detail, please find attachment.

Best Regards,
Yulia Yan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sarah2]

Thank you so much for your reply! I played around with it and was unsuccessful, so if you have time to continue to troubleshoot I would really appreciate it. 

While making the calculated column for "Month" I get an error that says "Column reference to 'Date' in table 'Table' cannot be used with a variation 'Month' because it does not have any." I then attempted to just use the Date column instead of month, but it did not work with the slicer.

Additionally, I would like to retain a continuous date slicer instead of just categorizing by month (this is not a deal breaker though). My main issue is that I have multiple years of data.

Thank you!