Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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

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.

 

 

DateWeek of YearWeekDay NameDay of WeekOfficeCard NumberDIstinct Staff Count / WeekOffices / Employee# Days Atttended / WeekEmployees / Office
1/3/2022101/03 - 01/07Monday1NEW YORK CITY2302663101163
1/11/2022201/10 - 01/14Tuesday2NEW YORK CITY2248776101163
1/18/2022301/17 - 01/21Tuesday2NEW YORK CITY2248790101163
1/20/2022301/17 - 01/21Thursday4NEW YORK CITY270449011163
1/24/2022401/24 - 01/28Monday1NEW YORK CITY2704412312163
1/26/2022401/24 - 01/28Wednesday3NEW YORK CITY106612311163
1/27/2022401/24 - 01/28Thursday4NEW YORK CITY2704412312163
1/28/2022401/24 - 01/28Friday5NEW YORK CITY29938123101163
1/31/2022501/31 - 02/04Monday1NEW YORK CITY2704414112163
2/1/2022501/31 - 02/04Tuesday2NEW YORK CITY2700814112163
2/1/2022501/31 - 02/04Tuesday2NEW YORK CITY2704414112163
2/1/2022501/31 - 02/04Tuesday2NEW YORK CITY28388141101163
2/2/2022501/31 - 02/04Wednesday3NEW YORK CITY22929141101163
2/3/2022501/31 - 02/04Thursday4NEW YORK CITY2700814112163
2/4/2022501/31 - 02/04Friday5NEW YORK CITY29938141101163
2/8/2022602/07 - 02/11Tuesday2NEW YORK CITY2700814212163
2/8/2022602/07 - 02/11Tuesday2NEW YORK CITY2704414212163
2/8/2022602/07 - 02/11Tuesday2NEW YORK CITY29938142101163
2/10/2022602/07 - 02/11Thursday4NEW YORK CITY2700814212163
2/10/2022602/07 - 02/11Thursday4NEW YORK CITY2704414212163
2/14/2022702/14 - 02/18Monday1NEW YORK CITY2704414113163
2/15/2022702/14 - 02/18Tuesday2NEW YORK CITY2700814112163
2/15/2022702/14 - 02/18Tuesday2NEW YORK CITY2704414113163
2/17/2022702/14 - 02/18Thursday4NEW YORK CITY2700814112163
2/17/2022702/14 - 02/18Thursday4NEW YORK CITY2704414113163
2/17/2022702/14 - 02/18Thursday4NEW YORK CITY27775141101163
2/24/2022802/21 - 02/25Thursday4NEW YORK CITY2704414511163
3/1/2022902/28 - 03/04Tuesday2NEW YORK CITY2704413613163
3/1/2022902/28 - 03/04Tuesday2NEW YORK CITY29938136102163
3/2/2022902/28 - 03/04Wednesday3NEW YORK CITY22929136102163
3/2/2022902/28 - 03/04Wednesday3NEW YORK CITY2704413613163
3/3/2022902/28 - 03/04Thursday4NEW YORK CITY2700813611163
3/3/2022902/28 - 03/04Thursday4NEW YORK CITY2704413613163
3/4/2022902/28 - 03/04Friday5NEW YORK CITY29938136102163
3/7/20221003/07 - 03/11Monday1NEW YORK CITY2704415913163
3/8/20221003/07 - 03/11Tuesday2NEW YORK CITY2700815912163
3/8/20221003/07 - 03/11Tuesday2NEW YORK CITY2704415913163
3/10/20221003/07 - 03/11Thursday4NEW YORK CITY2700815912163
3/10/20221003/07 - 03/11Thursday4NEW YORK CITY2704415913163
3/14/20221103/14 - 03/18Monday1NEW YORK CITY2704415414163
3/15/20221103/14 - 03/18Tuesday2NEW YORK CITY2700815412163
3/15/20221103/14 - 03/18Tuesday2NEW YORK CITY2704415414163
3/16/20221103/14 - 03/18Wednesday3NEW YORK CITY2704415414163
3/16/20221103/14 - 03/18Wednesday3NEW YORK CITY29938154101163
3/17/20221103/14 - 03/18Thursday4NEW YORK CITY22960154101163
3/17/20221103/14 - 03/18Thursday4NEW YORK CITY2700815412163
3/17/20221103/14 - 03/18Thursday4NEW YORK CITY2704415414163
3/21/20221203/21 - 03/25Monday1NEW YORK CITY2704414612163
3/22/20221203/21 - 03/25Tuesday2NEW YORK CITY2700814611163
3/22/20221203/21 - 03/25Tuesday2NEW YORK CITY2704414612163
3/23/20221203/21 - 03/25Wednesday3NEW YORK CITY22929146101163
3/25/20221203/21 - 03/25Friday5NEW YORK CITY29938146101163
3/28/20221303/28 - 04/01Monday1NEW YORK CITY2704414913163
3/29/20221303/28 - 04/01Tuesday2NEW YORK CITY2700814912163
3/29/20221303/28 - 04/01Tuesday2NEW YORK CITY2704414913163
3/31/20221303/28 - 04/01Thursday4NEW YORK CITY2700814912163
3/31/20221303/28 - 04/01Thursday4NEW YORK CITY2704414913163
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

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?

 

lbendlin_0-1660263600707.png

 

View solution in original post

3 REPLIES 3
lbendlin
Super User
Super User

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?

 

lbendlin_0-1660263600707.png

 

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.

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.