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 All,
I want to count the number of consecutive days for each attendance status for a specific employee. So here an example of the data:
Emp Id | Emp Name | Project Id | Program | Date | Schedule Status |
111111 | Max Mustermann | 55 | Test Progrma | 07/01/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/02/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/03/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/04/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/05/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/06/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/07/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/08/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/09/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/10/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/11/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/12/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/13/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/14/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/15/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/16/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/17/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/18/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/19/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/20/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/21/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/22/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/23/2021 | Work |
111111 | Max Mustermann | 55 | Test Progrma | 07/24/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/25/2021 | Off |
111111 | Max Mustermann | 55 | Test Progrma | 07/26/2021 | Unscheduled |
111111 | Max Mustermann | 55 | Test Progrma | 07/27/2021 | Unscheduled |
111111 | Max Mustermann | 55 | Test Progrma | 07/28/2021 | Unscheduled |
111111 | Max Mustermann | 55 | Test Progrma | 07/29/2021 | Unscheduled |
111111 | Max Mustermann | 55 | Test Progrma | 07/30/2021 | Unscheduled |
111111 | Max Mustermann | 55 | Test Progrma | 07/31/2021 | Unscheduled |
So as on output I want to have a matrix where under ROWs I will put - > Emp Name/Schedule Status and under values - the max consecutive days for each status. So the output will be for this case:
Name/ScheduleStatus | Max Consecutive |
Max Mustermann | |
"-Work" | 7 |
"-OFF" | 2 |
"-UNSCHEDULED" | 6 |
I would really appreciate if someone can help me with this - at a first glance it looks rather easy, but turns out to be quite hard, at least for me.
Thanks in advance!!
BEst regards,
Ivan
Solved! Go to Solution.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Worked perfectly!! Many thanks!
Just 1 question - what is the use of the index column in the Schedule Status table?
Thank you for your feedback.
The column - Work/Off/Unscheduled column - is sorted by Index column.
It does not have a problem with not having the index column. But the visualization would be shown like below.
Off / Unscheduled / Work ( alphabetical order)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
114 | |
99 | |
81 | |
70 | |
61 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |