Forum Discussion

carlochecchia's avatar
carlochecchia
Icon for Advocate I rankAdvocate I
5 years ago
Solved

Virtual Tables | Missing Employee

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!

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    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"
    )
    )
     
     
     
     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    5 years ago

     

    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"
    )
    )
     
     
     
     
    • carlochecchia's avatar
      carlochecchia
      Icon for Advocate I rankAdvocate I
      5 years ago

      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?