Virtual Tables | Missing Employee

[carlochecchia]

Hi Everyone,

I have a merged dataset that combines several forms that my coworkers filled out a regarding the time they started and finished their shifts.
I have set up the data refresh so it continuously updates the information every half hour  from One drive.

ISSUE: I asked the coworkers to fill this out every day they worked however there are some that often forget.

I would like to create 2 virtual tables:
VirtualTBL1 = list of employees who worked (filled out the sheet) TODAY()
VirtualTBL2 = list of employees who worked (filled out the sheet) the working day before

Then I would like to compare the 2 Virtual Tables and return all the rows that exist in VirtualTBL2 and don´t show in VirtualTBL1.
( So i could see who forgot to fill out the form or any new employees)

Here is a Mock Data.

	Date	Start	Finish
Paul	09/ July /2021 (friday)	08:00	12:00
John	09/ July /2021(friday)	08:00	12:00
Marie	09/ July /2021(friday)	08:00	12:00
Paul	12/ July /2021(monday)	08:00	12:00
John	12/ July /2021(monday)	08:00	12:00

Result I expect:

On 12/July/2021 (Monday), at the end of the Day, I should see a list with Marie’s name in it.

Paul, John and Marie worked the last working day (list with 3 names) compared to
a list that says that only Paul and John worked today() ‘12/July/2021’..

Therefore, only Marie’s name will be missing and that’s what I should see

Thanks in advance for the help!

[Jihwan_Kim · Accepted solution]

Who forgot to fill out the form? : =

VAR _lastdate =

CALCULATE ( MAX ( Data[Date] ), REMOVEFILTERS ( Data[Name] ) )

VAR _previousworkingdate =

CALCULATE (

MAX ( Dates[Date] ),

FILTER (

Dates,

Dates[Date] < _lastdate

&& NOT ( Dates[Day of Week] IN { 0, 6 } )

)

)

VAR _employeeslastdatelist =

CALCULATETABLE (

VALUES ( Data[Name] ),

FILTER ( RELATEDTABLE ( Data ), Data[Date] = _lastdate )

)

VAR _employeespreviousworkingdatelist =

CALCULATETABLE (

VALUES ( Data[Name] ),

FILTER ( RELATEDTABLE ( Data ), Data[Date] = _previousworkingdate )

)

VAR _fillintheformeveryworkingday =

INTERSECT ( _employeeslastdatelist, _employeespreviousworkingdatelist )

RETURN

IF (

HASONEVALUE ( Names[Name] ),

IF (

COUNTROWS ( _fillintheformeveryworkingday ) = 1,

"fill in on last date",

"forget to fill in"

)

)

Link to the pbix file 

[carlochecchia]

Hi Jihwan_Kim, one again thank you for the quick response.

Is there a way that I can show a list of only the people who "Forgot to fill in"? Instead of showing John Maire and Paul, show only Maire..
The way it's set up now, it shows a list of all the coworkers who have ever appeared in the list.

The issue  is I have some coworkers who have filled out this forms before but left the company over a month ago. I don't want to see that person's name in the list, since it will always show that he "Forgot to fill in" since he no longer works with us.

Thats why I thought of comparing only with the ones who filled in the forms the day before vs who filled in today(). Would it be possible?