Count consecutive days on a specific status

[IPGeorgiev]

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

[Jihwan_Kim · Accepted solution]

Link to the PBIX file 

[IPGeorgiev]

Worked perfectly!! Many thanks!

Just 1 question - what is the use of the index column in the Schedule Status table?

[Jihwan_Kim]

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)