Count consecutive days with absence including weekend

[Anonymous]

Hello,

I am trying to find a way of calculating the amount of consecutive days employees where absent for sickeness, including weekends.

• For Adeline the payrool has put the right information of sickness absence including the weekend but for Jean I don't have this data.
• How can I get this value including the weekend if there is no value added in the database ? (e.g. if an employee was sick on Friday and is still sick on Monday, we must add the weekend and still continue counting)
• Next, I would like to add a new column to subtract the first 2 days of each absence period that are not paid by the company.
  The first screenshot shows the data I need to get and the second is my data model (except for the red columns).

Thank you in advance for your help.

Date	Nom du jour	n° d'employé A	Nom Prénom	Absence 1	Durée 1	Consecutive absent	Consecutive absence - 2 days
21.01.2023	samedi	01	Adeline XXX		0
22.01.2023	dimanche	01	Adeline XXX		0
23.01.2023	lundi	01	Adeline XXX		0
24.01.2023	mardi	01	Adeline XXX		0
25.01.2023	mercredi	01	Adeline XXX		0
26.01.2023	jeudi	01	Adeline XXX		0
27.01.2023	vendredi	01	Adeline XXX		0
28.01.2023	samedi	01	Adeline XXX		0
29.01.2023	dimanche	01	Adeline XXX		0
30.01.2023	lundi	01	Adeline XXX	Maladie	8	1	0
31.01.2023	mardi	01	Adeline XXX	Maladie	8	2	0
01.02.2023	mercredi	01	Adeline XXX	Maladie	8	3	1
02.02.2023	jeudi	01	Adeline XXX	Maladie	8	4	2
03.02.2023	vendredi	01	Adeline XXX	Maladie	8	5	3
04.02.2023	samedi	01	Adeline XXX	Maladie	0	6	4
05.02.2023	dimanche	01	Adeline XXX	Maladie	0	7	5
06.02.2023	lundi	01	Adeline XXX	Maladie	8	8	6
07.02.2023	mardi	01	Adeline XXX	Maladie	8	9	7
08.02.2023	mercredi	01	Adeline XXX	Maladie	8	10	8
09.02.2023	jeudi	01	Adeline XXX		0
10.02.2023	vendredi	01	Adeline XXX		0
11.02.2023	samedi	01	Adeline XXX		0
21.01.2023	samedi	02	Jean DE		0
22.01.2023	dimanche	02	Jean DE		0
23.01.2023	lundi	02	Jean DE	Maladie	8	1	0
24.01.2023	mardi	02	Jean DE	Maladie	8	2	0
25.01.2023	mercredi	02	Jean DE	Maladie	8	3	1
26.01.2023	jeudi	02	Jean DE	Maladie	8	4	2
27.01.2023	vendredi	02	Jean DE	Maladie	8	5	3
28.01.2023	samedi	02	Jean DE		0	6	4
29.01.2023	dimanche	02	Jean DE		0	7	5
30.01.2023	lundi	02	Jean DE	Maladie	8	8	6
31.01.2023	mardi	02	Jean DE	Maladie	8	9	7
01.02.2023	mercredi	02	Jean DE		0
04.02.2023	samedi	02	Jean DE		0
05.02.2023	dimanche	02	Jean DE		0
06.02.2023	lundi	02	Jean DE		0
07.02.2023	mardi	02	Jean DE		0
08.02.2023	mercredi	02	Jean DE		0
09.02.2023	jeudi	02	Jean DE		0
05.03.2023	dimanche	02	Jean DE		0
06.03.2023	lundi	02	Jean DE	Maladie	8	1	0
07.03.2023	mardi	02	Jean DE		0

[MAwwad]

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.

[Anonymous]

Hello @MAwwad ,

Do you have an idea ?

Thanks a lot,

[Anonymous]

Hello @MAwwad ,

Thanks for your help.

I tried these calculated columns but didn't get the right result.

I don't have Table1[Consecutive absent] in my data model. what data should I take in this specific case?

for this person, I should have 10 days consecutive sick days.

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)