cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
durtiaga
Frequent Visitor

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

1 ACCEPTED SOLUTION
durtiaga
Frequent Visitor

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

View solution in original post

4 REPLIES 4
durtiaga
Frequent Visitor

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

View solution in original post

TomMartens
Super User II
Super User II

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
amitchandak
Super User IV
Super User IV

@durtiaga , refer if my blog can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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.

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors