Count consecutive days with absence including weekend

Salut

To calculate the number of consecutive sick days for each employee, including weekends, and to add 2 days to each period of absence that is not paid by the company, you can follow these steps:

1. Create a calculated column in your table to mark the days where an employee was absent due to sickness. Use the following formula:

    

   Absence Marker = IF(Table1[Absence 1] = "Maladie", 1, 0)

   This will create a new column named "Absence Marker" that will have a value of 1 if the employee was absent due to sickness on a particular day, and 0 otherwise.

2. Create another calculated column to count the number of consecutive sick days for each employee. Use the following formula:

    

   Consecutive Sick Days = VAR currentRow = Table1[Date] VAR employeeID = Table1[n° d'employé A] VAR startDate = currentRow - Table1[Consecutive absent] VAR endDate = currentRow VAR weekends = 2 * COUNTROWS( FILTER( CALENDAR(startDate, endDate), WEEKDAY([Date], 2) >= 6 ) ) VAR count = SUMX( FILTER( Table1, Table1[n° d'employé A] = employeeID && Table1[Date] >= startDate && Table1[Date] <= endDate && Table1[Absence Marker] = 1 ), 1 ) RETURN count + weekends + IF(count > 0, 2, 0)

   The formula then returns the count of consecutive sick days plus the number of weekend days plus 2 days if the employee was absent for at least one day.

3. Create a table visual in Power BI and add the following fields to it:

   • Nom Prénom (Employee name)
   • Date
   • Consecutive Sick Days

   You can then filter the table by employee or date as needed.

Note: The formula assumes that the "Date" column is of type "Date" in your table. If it's not, you may need to adjust the formula accordingly. Also, the formula assumes that weekends are Saturday and Sunday. If your company has a different weekend schedule, you'll need to adjust the formula accordingly.