Forum Discussion

durtiaga's avatar
durtiaga
Frequent Visitor
5 years ago
Solved

Consecutive days worked with reset

Hi all,

 

I have being using for a while a formula in excel to calculate the days worked in a row, but the number of rows now is too big to handle with excel, so I'm trying to get the same result with BI.

 

The excel formula was really simple:

=IF((I4+1)<>I5,1,L4+1)

 

The data is ordered by Employee ID and then, by date. So when the employee changes, the date is also restarted. The count must include both weekdays and weekends, since the idea is to found the people that is now resting.

 

I have searched for hours and tried different solutions found here, but no one worked for me. 

The closest I get to it is following this article, which helped me to create a column just with the last date before each reset. But I cannot get the difference between the start date and end date of each row.

https://community.powerbi.com/t5/Desktop/dax-grouping-consecutive-days/td-p/488880

 

The expected result would be the next:

 

111113

21/01/2021

1

111115

01/05/2020

1

111115

02/05/2020

2

111115

03/05/2020

3

111115

04/05/2020

4

12

08/05/2020

1

12

09/05/2020

2

12

10/05/2020

3

12

11/05/2020

4

 

Or even better, just with the total days worked and the others column blank:

 

111113

21/01/2021

1

111115

01/05/2020

 

111115

02/05/2020

 

111115

03/05/2020

 

111115

04/05/2020

4

12

08/05/2020

 

12

09/05/2020

 

12

10/05/2020

 

12

11/05/2020

4

 

Thank you very much for your support.
Best regards

  • Hi,

     

    Finally I have found a solution modificating slightly the steps from the thread indicated on the first post. The step with the index didn't work fine for me so I used the Index created on the first steps and got solved.

     

    Thanks for the comments.

    Best,

     

    Thanks

4 Replies