Consecutive days worked with reset

[durtiaga]

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

[durtiaga · Accepted solution]

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

[TomMartens]

Hey @durtiaga ,

please create a pbix that contains sample data, but still reflects your data model, upload the pbix to onedrive or dropbox and share the link. If you are using Excel to create the sample data, share the xlsx as well.

Regards,

Tom

[amitchandak]

@durtiaga , refer if my blog can help

https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...

[durtiaga]

Hi @amitchandak 

Thanks for your comment.

My data has 1 column for dates with a row per day, so that solution would not fit in this case.