Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Dear friends,
can you please help me? I need to calculate the hours of absenteism per day. The data that I can get is the following:
Name | Total Days off | Start date |
A | 2 | 16.01.2023 |
A | 1 | 02.03.2023 |
B | 3 | 19.12.2022 |
B | 1 | 03.02.2023 |
C | 3 | 01.06.2023 |
C | 2 | 12.01.2023 |
The result expected is the following: One line for each day off that are only a workday (I already have a dimdate with a column "workday" = true or false))
Please find below the expected table:
Name | Total Days off | Start date |
A | 1 | 16.01.2023 |
A | 1 | 17.01.2023 |
A | 1 | 02.03.2023 |
B | 1 | 19.12.2022 |
B | 1 | 20.12.2022 |
B | 1 | 21.12.2022 |
B | 1 | 03.02.2023 |
C | 1 | 01.06.2023 |
C | 1 | 02.06.2023 |
C | 1 | 05.06.2023 |
C | 1 | 12.01.2023 |
C | 1 | 13.01.2023 |
I would really appreciate your help 🙂
Kind regards,
In order to create a visual per day and per team of the capacity. Thanks
Please read what I write some hours before. It is not a easy question and you have to think about your data model. Also "In order to create a visual per day and per team of the capacity" . You have to define capacity of the team. Perhaps you show us first your underlaying data structure with some examples (best case with the internal table function, i hate it to copy data from screenshots....)
Proud to be a Super User!
Hello,
there is an additionnal colum to this table "Hours per day". The goal is to calculate the sum of hours of absenteism.
Name | Total Days off | Hours | Start date |
A | 1 | 7.90 | 16.01.2023 |
A | 1 | 7.90 | 17.01.2023 |
A | 1 | 7.90 | 02.03.2023 |
B | 1 | 7.90 | 19.12.2022 |
B | 1 | 7.90 | 20.12.2022 |
B | 1 | 7.90 | 21.12.2022 |
B | 1 | 7.90 | 03.02.2023 |
C | 1 | 7.90 | 01.06.2023 |
C | 1 | 7.90 | 02.06.2023 |
C | 1 | 7.90 | 05.06.2023 |
C | 1 | 7.90 | 12.01.2023 |
C | 1 | 7.90 | 13.01.2023 |
Perfect, I had the right feeling. If you have a question next time, write directly the goal of the task and do not only describe your solution (additional a solution is great!). Sometimes your solution is the wrong way and ppl invest time to help and than we go in the wrong direction. 🙂
You have to think about a data model. You can than easy calculate sum of hours of absenteism.
Create a table for the date (dimensional table) with the flag of a working day. Be aware of if a person A B C have perhaps different working days? Are the dayoff hours different from person and days?
If you share a good data example for the underlaying data someone can help you. You can upload to one drive, google drive, dropbox your excel and share here the link.
Proud to be a Super User!
I have the feeling that you have to tell us more where do you want to use this new table.
Perhaps it is only a "middle part" of a more complex question. Can you please tell us more about the reason for this new table? Thank you 🙂
Proud to be a Super User!
User | Count |
---|---|
54 | |
23 | |
19 | |
18 | |
17 |
User | Count |
---|---|
91 | |
87 | |
47 | |
30 | |
22 |