Forum Discussion

maart666's avatar
maart666
Helper I
4 years ago
Solved

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.

 
 
 
 
 
 
 

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

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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

 

 

 

 

 

 

 

I attach the file in PBI. 

Thank you in advance.

 

Marcin

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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/1290346

     

    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

    • maart666's avatar
      maart666
      Helper I
      4 years ago

      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.


       

       

       

       

       

       

       

       

       

       

       

       

       

       

       

      Thank you!

      • maart666's avatar
        maart666
        Helper I
        4 years ago

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

        Thank you!