Days not booked per worker (HR)

[Syndicate_Admin]

Good morning

I need help with the following:

I have two tables:

1. SIGNINGS Table: gathers the signings of each worker.

2. CALENDAR table: calendar.

Relationships: FILES[Date Signing] is related to CALENDAR[Date].

I need to get the days that each worker has not signed. For example:

Maria ---> has not signed on 29/03/2023

Carla ---> has not signed on 20/03/2023

Eduardo ---> has not signed on 18/03/2023

When a worker does not register, the date is not recorded in FILES[Date Recorded].

Can you help me, please?

Thank you!

[Adamboer]

To get the days that each worker has not signed, you can follow these steps:

1. Create a query that joins the SIGNINGS table with the CALENDAR table using the relationship between FILES[Date Signing] and CALENDAR[Date].

2. Use a LEFT JOIN to include all the dates in the CALENDAR table, regardless of whether there is a corresponding record in the SIGNINGS table.

3. Add a calculated field to the query that determines whether a signing has occurred on a given day. This field can use the IIf() function to check whether there is a value in FILES[Date Recorded] for the given worker and date.

4. Group the query by worker and date, and use the SUM() function to count the number of signings that occurred on each day.

5. Use a WHERE clause to filter the results to only include days where no signing occurred.

6. Format the query results to display the worker name and the date(s) they did not sign.

Here's an example SQL code for this query:
SELECT SIGNINGS.Worker, CALENDAR.Date
FROM CALENDAR LEFT JOIN SIGNINGS ON CALENDAR.Date = SIGNINGS.[Date Signing]
GROUP BY SIGNINGS.Worker, CALENDAR.Date
HAVING SUM(IIf(ISNULL(FILES.[Date Recorded]), 0, 1)) = 0

This query will return the worker name and date(s) where no signing occurred.

[Syndicate_Admin]

@Syndicate_Admin

I have made a test model for the same casuistry.

In the attached table, you can see that Maria has not signed on 30/03/2023.

You would need to have a table where there were 3 columns: the calendar column, the user column, and the tab date column.

I mean, I don't know how to make that, even if Maria hasn't signed, create a blank record for me, for example I would need something like this:

Thanks and greetings!

[Syndicate_Admin]

@Syndicate_Admin excuse me, but I've been using Power BI for a short time and I don't quite understand what needs to be done...

I have done the following:

1. I have combined the CALENDAR table with the LEFT JOIN SIGNINGS table. Now I have two columns type Date and a column with the users.

I'm not going to continue, I don't understand.

[Idrissshatila]

Hello @Syndicate_Admin ,

Try this

Measure= CALCULATE ( COUNTROWS ( FILES ), FILES[Date Recorded] = BLANK () )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin

[Syndicate_Admin]

@Syndicate_Admin the problem is that when they don't sign, no row is created, so there are no blanks either.