Forum Discussion

TomiKasurinen's avatar
TomiKasurinen
Frequent Visitor
4 years ago
Solved

Calculating sick leave duration

Hello!

 

Is there a way to calculate a sick leave duration (in days, shift duration is not needed here) for a single employee?

 

Data sample simplified:

 

 

Index is added in power query based on employee/date.

 

BR,

Tomi

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi TomiKasurinen ,

     

    I use this table to test, please create this calculated column.

    Table.

    Duration of sick leave = 
VAR _date = 
CALCULATE(
    MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Work code]<>"Sick"
                &&'Table'[Date]<=EARLIER('Table'[Date])
                &&'Table'[Employee]=EARLIER('Table'[Employee])
        )
)
VAR _times = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])
            &&'Table'[Date]>_date
            &&'Table'[Employee]=EARLIER('Table'[Employee])
    )
)
RETURN
_times

    Attached PBIX file for reference.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi TomiKasurinen ,

     

    I use this table to test, please create this calculated column.

    Table.

    Duration of sick leave = 
VAR _date = 
CALCULATE(
    MAX('Table'[Date]),
        FILTER(
            ALL('Table'),
            'Table'[Work code]<>"Sick"
                &&'Table'[Date]<=EARLIER('Table'[Date])
                &&'Table'[Employee]=EARLIER('Table'[Employee])
        )
)
VAR _times = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALL('Table'),
        'Table'[Date]<=EARLIER('Table'[Date])
            &&'Table'[Date]>_date
            &&'Table'[Employee]=EARLIER('Table'[Employee])
    )
)
RETURN
_times

    Attached PBIX file for reference.

     

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly -- How to provide sample data

    • VCaley's avatar
      VCaley
      New Member
      2 years ago

      Hi, this worked great for me! Thank you 

       

      Is there a way to turn it into counting calendar days? So for example if someone is off Thu, Fri, Mon, Tue, it would count the weekend days too?  I am assuming it would be adding in a reference to the Calendar table - but I cannot work out how or where?

      I would also like there to be a way to differenciate between sickness periods

      Any help would be amazing - sorry for the questions, I'm fairly new at this!