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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
maart666
Helper I
Helper I

Summarizing Continuous Date Ranges

 

Hello,
I have a base with employee absences. After returning to work after 30 continuous absences, the employee must be sent for an examination. Some employees have several lines with absences, if the date of the beginning of the next absence is equal to the date of the end of the previous absence + 1, I have to sum up such absences.
Przechwytywanie1.PNG
 
 
 
 
 
 
 

unfortunately, Saturdays and Sundays are sometimes subtracted in these ranges:

 

Przechwytywanie.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Ideally, the table in the report should only show employees who have had a continuous absence of 30 days or more.
Przechwytywanie2.PNG

 

 

 

 

 

 

 

I attach the file in PBI. 

Thank you in advance.

 

Marcin

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@maart666 , Using this blog, you should get new start and new end date, when there is split

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

 

Now using the distinct of that using summarize , calculate work day using calendar

 

Sumx(Addcolumns(Summarize(Table, Table[Employee], Table[New Start], New[End Date]) , "_1", COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([New Start Date],[New End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)) ), [_1])

 

Also refer

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@maart666 , Using this blog, you should get new start and new end date, when there is split

https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...

 

Now using the distinct of that using summarize , calculate work day using calendar

 

Sumx(Addcolumns(Summarize(Table, Table[Employee], Table[New Start], New[End Date]) , "_1", COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR([New Start Date],[New End Date]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1)) ), [_1])

 

Also refer

How to calculate Business Days/ Workdays, with or without date table: https://youtu.be/Qv4wT8_P-AA

Thank you amitchandak!

for the first part works perfekt!

 

But when but are you able to help with the solution when the days when absences are not entered as a continuous range, but only for working days. The range starts on Monday and ends on Friday.
So as a continuity I have to treat the Friday date and the date of the next Monday.


Przechwytywanie.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Thank you!

Hi amitchandak, 
I was able to adjust your solution to my needs.

Thank you!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors