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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

TomMartens
Super User
Super User

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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