Attendance report that shows daily employee attendance even if no data was recorded

[Anonymous]

Hi,

I am trying to put together an attendance report that tells me what days an employee attended the office per week. I only have data collected for days where they came in, and I would like to trasnform or visualize the data so that it displays a column for "attended" or "did not attend." This would allow me to add slicers that narrow down the data in the report even for days with no data.

I tried using crossjoin so that all names are assigned a day of the year, and I was trying to find a way to combine that with the original data set so that I can clean the data and have a sort of date table with all employees. This was close to what I wanted but I am left with a lot of null values for the rest of the columns since I only use the name and a date table to create a the name-date table. It is important that the data also lets me create a slicer specifically for " attended" or "did not attend" and for me to count "days attended per week" so I can filter the data down to values 0-5 days/week.

Ultimately, I want each name to have a daily data entry but I am not sure how I can add the missing days with no data into the original set. Does anyone have a solution for this? I included some sample data - the card numbers correspond to individual employees.

Date	Week of Year	Week	Day Name	Day of Week	Office	Card Number	DIstinct Staff Count / Week	Offices / Employee	# Days Atttended / Week	Employees / Office
1/3/2022	1	01/03 - 01/07	Monday	1	NEW YORK CITY	23026	63	10	1	163
1/11/2022	2	01/10 - 01/14	Tuesday	2	NEW YORK CITY	22487	76	10	1	163
1/18/2022	3	01/17 - 01/21	Tuesday	2	NEW YORK CITY	22487	90	10	1	163
1/20/2022	3	01/17 - 01/21	Thursday	4	NEW YORK CITY	27044	90	1	1	163
1/24/2022	4	01/24 - 01/28	Monday	1	NEW YORK CITY	27044	123	1	2	163
1/26/2022	4	01/24 - 01/28	Wednesday	3	NEW YORK CITY	1066	123	1	1	163
1/27/2022	4	01/24 - 01/28	Thursday	4	NEW YORK CITY	27044	123	1	2	163
1/28/2022	4	01/24 - 01/28	Friday	5	NEW YORK CITY	29938	123	10	1	163
1/31/2022	5	01/31 - 02/04	Monday	1	NEW YORK CITY	27044	141	1	2	163
2/1/2022	5	01/31 - 02/04	Tuesday	2	NEW YORK CITY	27008	141	1	2	163
2/1/2022	5	01/31 - 02/04	Tuesday	2	NEW YORK CITY	27044	141	1	2	163
2/1/2022	5	01/31 - 02/04	Tuesday	2	NEW YORK CITY	28388	141	10	1	163
2/2/2022	5	01/31 - 02/04	Wednesday	3	NEW YORK CITY	22929	141	10	1	163
2/3/2022	5	01/31 - 02/04	Thursday	4	NEW YORK CITY	27008	141	1	2	163
2/4/2022	5	01/31 - 02/04	Friday	5	NEW YORK CITY	29938	141	10	1	163
2/8/2022	6	02/07 - 02/11	Tuesday	2	NEW YORK CITY	27008	142	1	2	163
2/8/2022	6	02/07 - 02/11	Tuesday	2	NEW YORK CITY	27044	142	1	2	163
2/8/2022	6	02/07 - 02/11	Tuesday	2	NEW YORK CITY	29938	142	10	1	163
2/10/2022	6	02/07 - 02/11	Thursday	4	NEW YORK CITY	27008	142	1	2	163
2/10/2022	6	02/07 - 02/11	Thursday	4	NEW YORK CITY	27044	142	1	2	163
2/14/2022	7	02/14 - 02/18	Monday	1	NEW YORK CITY	27044	141	1	3	163
2/15/2022	7	02/14 - 02/18	Tuesday	2	NEW YORK CITY	27008	141	1	2	163
2/15/2022	7	02/14 - 02/18	Tuesday	2	NEW YORK CITY	27044	141	1	3	163
2/17/2022	7	02/14 - 02/18	Thursday	4	NEW YORK CITY	27008	141	1	2	163
2/17/2022	7	02/14 - 02/18	Thursday	4	NEW YORK CITY	27044	141	1	3	163
2/17/2022	7	02/14 - 02/18	Thursday	4	NEW YORK CITY	27775	141	10	1	163
2/24/2022	8	02/21 - 02/25	Thursday	4	NEW YORK CITY	27044	145	1	1	163
3/1/2022	9	02/28 - 03/04	Tuesday	2	NEW YORK CITY	27044	136	1	3	163
3/1/2022	9	02/28 - 03/04	Tuesday	2	NEW YORK CITY	29938	136	10	2	163
3/2/2022	9	02/28 - 03/04	Wednesday	3	NEW YORK CITY	22929	136	10	2	163
3/2/2022	9	02/28 - 03/04	Wednesday	3	NEW YORK CITY	27044	136	1	3	163
3/3/2022	9	02/28 - 03/04	Thursday	4	NEW YORK CITY	27008	136	1	1	163
3/3/2022	9	02/28 - 03/04	Thursday	4	NEW YORK CITY	27044	136	1	3	163
3/4/2022	9	02/28 - 03/04	Friday	5	NEW YORK CITY	29938	136	10	2	163
3/7/2022	10	03/07 - 03/11	Monday	1	NEW YORK CITY	27044	159	1	3	163
3/8/2022	10	03/07 - 03/11	Tuesday	2	NEW YORK CITY	27008	159	1	2	163
3/8/2022	10	03/07 - 03/11	Tuesday	2	NEW YORK CITY	27044	159	1	3	163
3/10/2022	10	03/07 - 03/11	Thursday	4	NEW YORK CITY	27008	159	1	2	163
3/10/2022	10	03/07 - 03/11	Thursday	4	NEW YORK CITY	27044	159	1	3	163
3/14/2022	11	03/14 - 03/18	Monday	1	NEW YORK CITY	27044	154	1	4	163
3/15/2022	11	03/14 - 03/18	Tuesday	2	NEW YORK CITY	27008	154	1	2	163
3/15/2022	11	03/14 - 03/18	Tuesday	2	NEW YORK CITY	27044	154	1	4	163
3/16/2022	11	03/14 - 03/18	Wednesday	3	NEW YORK CITY	27044	154	1	4	163
3/16/2022	11	03/14 - 03/18	Wednesday	3	NEW YORK CITY	29938	154	10	1	163
3/17/2022	11	03/14 - 03/18	Thursday	4	NEW YORK CITY	22960	154	10	1	163
3/17/2022	11	03/14 - 03/18	Thursday	4	NEW YORK CITY	27008	154	1	2	163
3/17/2022	11	03/14 - 03/18	Thursday	4	NEW YORK CITY	27044	154	1	4	163
3/21/2022	12	03/21 - 03/25	Monday	1	NEW YORK CITY	27044	146	1	2	163
3/22/2022	12	03/21 - 03/25	Tuesday	2	NEW YORK CITY	27008	146	1	1	163
3/22/2022	12	03/21 - 03/25	Tuesday	2	NEW YORK CITY	27044	146	1	2	163
3/23/2022	12	03/21 - 03/25	Wednesday	3	NEW YORK CITY	22929	146	10	1	163
3/25/2022	12	03/21 - 03/25	Friday	5	NEW YORK CITY	29938	146	10	1	163
3/28/2022	13	03/28 - 04/01	Monday	1	NEW YORK CITY	27044	149	1	3	163
3/29/2022	13	03/28 - 04/01	Tuesday	2	NEW YORK CITY	27008	149	1	2	163
3/29/2022	13	03/28 - 04/01	Tuesday	2	NEW YORK CITY	27044	149	1	3	163
3/31/2022	13	03/28 - 04/01	Thursday	4	NEW YORK CITY	27008	149	1	2	163
3/31/2022	13	03/28 - 04/01	Thursday	4	NEW YORK CITY	27044	149	1	3	163

[lbendlin · Accepted solution]

Thank you for the sample data.  However it already seems to be mixed with aggregated data at day level with some summary columns.  I removed most of the columns and olny kept the day and the card number.

Is this what you are looking for?

[creativeuser1]

That is exactly what I am looking for, too. Is there a solution where you can output only the empty fields? So that you can see at a glance who was absent on which days? With a list of over 300 employees, this quickly becomes unmanageable. I have tried to filter, but then the whole table is always empty.

[lbendlin]

To report on things that are not there you need to use crossjoins as demonstrated in the sample solution. You may want to open a new thread with your sample data and expected outcome.