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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
amitchandak
Super User
Super User

@durtiaga , refer if my blog can help

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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