Power bi Calculate the Monday dates

[K_Stevs1]

Hi Is there a way in power bi to calculate the Monday's covered between two dates, if annual leave or sickness cover more than 1 week? I have also asked if it is possible in Excel, but then Power Bi, came to mind as well.

Thanks for any advice.

Demo.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Absence Category	Absence Start Date	Absence End Date	Monday Date	Total Duration (Days)	Total Absence Duration (Hours)	Total Absence Duration (Days)	FTE	WTE	Hours	First Day Absent
2	Annual Leave	06/05/2024 00:00	02/06/2024 00:00	06/05/2024	28	150.00	28	1.00	37.50	7.50	Mon
3	Annual Leave	15/04/2024 00:00	12/05/2024 00:00	15/04/2024	28	120.00	28	0.80	37.50	7.50	Mon
4	Annual Leave	01/02/2024 00:00	28/02/2024 00:00	29/01/2024	28	92.00	28	0.61	37.50	7.50	Thu
5	Sickness	25/12/2023 00:00	21/01/2024 00:00	25/12/2023	28	9.20	28	0.80	23.00	4.60	Mon
6	Sickness	15/07/2024 00:00	11/08/2024 00:00	15/07/2024	28	9.20	28	1.00	23.00	4.60	Mon
7	Sickness	15/07/2024 00:00	11/08/2024 00:00	15/07/2024	28	9.20	28	1.00	23.00	4.60	Mon
8	Annual Leave	22/07/2024 00:00	18/08/2024 00:00	22/07/2024	28	150.00	28	1.00	34.50	6.90	Mon
Sheet1

 

[GraH]

One way in Excel.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Absence Category	Absence Start Date	Absence End Date	Monday Date	Total Duration (Days)	Total Absence Duration (Hours)	Total Absence Duration (Days)	FTE	WTE	Hours	First Day Absent	# Mondays
2	Annual Leave	6/05/2024 0:00	2/06/2024 0:00	6/05/2024	28	150.00	28	1.00	37.50	7.50	Mon	4
3	Annual Leave	15/04/2024 0:00	12/05/2024 0:00	15/04/2024	28	120.00	28	0.80	37.50	7.50	Mon	4
4	Annual Leave	1/02/2024 0:00	28/02/2024 0:00	29/01/2024	28	92.00	28	0.61	37.50	7.50	Thu	4
5	Sickness	25/12/2023 0:00	21/01/2024 0:00	25/12/2023	28	9.20	28	0.80	23.00	4.60	Mon	4
6	Sickness	15/07/2024 0:00	11/08/2024 0:00	15/07/2024	28	9.20	28	1.00	23.00	4.60	Mon	4
7	Sickness	15/07/2024 0:00	11/08/2024 0:00	15/07/2024	28	9.20	28	1.00	23.00	4.60	Mon	4
8	Annual Leave	22/07/2024 0:00	18/08/2024 0:00	22/07/2024	28	150.00	28	1.00	34.50	6.90	Mon	4
Sheet1

Cell Formulas
Range	Formula
L2:L8	L2	=IF((C2-B2)>7,SUM((WEEKDAY(SEQUENCE(C2-B2,,B2,1),2)=1)/1),0)

With PowerBI you probably need a date table. Is it available in your data model?

Or maybe this can help= Creating a List of Numbers or Dates in Power BI using GenerateSeries Function in DAX