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

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.

Reply
IPGeorgiev
Helper III
Helper III

Count consecutive days on a specific status

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 IdEmp NameProject IdProgramDateSchedule  Status
111111Max Mustermann55Test Progrma07/01/2021Off
111111Max Mustermann55Test Progrma07/02/2021Work
111111Max Mustermann55Test Progrma07/03/2021Work
111111Max Mustermann55Test Progrma07/04/2021Work
111111Max Mustermann55Test Progrma07/05/2021Work
111111Max Mustermann55Test Progrma07/06/2021Work
111111Max Mustermann55Test Progrma07/07/2021Work
111111Max Mustermann55Test Progrma07/08/2021Off
111111Max Mustermann55Test Progrma07/09/2021Off
111111Max Mustermann55Test Progrma07/10/2021Work
111111Max Mustermann55Test Progrma07/11/2021Work
111111Max Mustermann55Test Progrma07/12/2021Work
111111Max Mustermann55Test Progrma07/13/2021Work
111111Max Mustermann55Test Progrma07/14/2021Work
111111Max Mustermann55Test Progrma07/15/2021Work
111111Max Mustermann55Test Progrma07/16/2021Work
111111Max Mustermann55Test Progrma07/17/2021Off
111111Max Mustermann55Test Progrma07/18/2021Off
111111Max Mustermann55Test Progrma07/19/2021Work
111111Max Mustermann55Test Progrma07/20/2021Work
111111Max Mustermann55Test Progrma07/21/2021Work
111111Max Mustermann55Test Progrma07/22/2021Work
111111Max Mustermann55Test Progrma07/23/2021Work
111111Max Mustermann55Test Progrma07/24/2021Off
111111Max Mustermann55Test Progrma07/25/2021Off
111111Max Mustermann55Test Progrma07/26/2021Unscheduled
111111Max Mustermann55Test Progrma07/27/2021Unscheduled
111111Max Mustermann55Test Progrma07/28/2021Unscheduled
111111Max Mustermann55Test Progrma07/29/2021Unscheduled
111111Max Mustermann55Test Progrma07/30/2021Unscheduled
111111Max Mustermann55Test Progrma07/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/ScheduleStatusMax 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

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture1.png

 

Link to the PBIX file 

 

 

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.


Go to My LinkedIn Page


View solution in original post

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Picture1.png

 

Link to the PBIX file 

 

 

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.


Go to My LinkedIn Page


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.


Go to My LinkedIn Page


Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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