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.

[v-mengzhu-msft]

Hi @Anonymous ,

I'm sorry I don't clearly understand what you mean, are you trying to count the number of days an employee works per week? I see this column in your sample data.

Is this the number of days worked?

Also, I have a question about what you mean "add the missing days with no data into the original set"?

I'm sorry there are so many things I don't understand and I would be very happy if you could answer my doubts.

Best regards,

Community Support Team Selina zhu

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

[Anonymous]

Hi thank you for your response!

Yes I am trying to find the number of days an employee attended per week for every week of the year. o I want a column telling me what day of the week it is, and a column with the # of days attended per week as seen in the table below. 

I also want to see a column for each day of the week telling me if they attended for that day : (e.g. Y/N). Currently I only have days when they did attend and I want to add days when they didn't. 

By missing days, I mean the days where an employee didn't show up. So If they showed up 8/16 but not 8/17, they only appear for 8/16 on the data set. But I want the table to also show a value for 8/17 telling me they did not attend. 

Sorry the data set isn't the best to work with. I may repost with a new data set or rephrase the question. Thank you again!

[v-mengzhu-msft]

Hi @Anonymous ,

Thank you very much for your patience and I look forward to receiving a sample data or a PBIX file (make sure not to include private data) so that I can better help you with your test calculations.😊

Best regards,

Community Support Team Selina zhu

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

[Anonymous]

Here is some sample data:

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