Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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?
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |