Return all week numbers in a range

[shawndempsey7]

I have a large number of start and stop dates that align with employee identification numbers, and I want a count of all of the weeks that their ranges cover. Can I do this in Power BI? Thank you!

Example

Employee #256

start date=1/1/25

end date=1/21/25

Employee #257

start date=1/1/25

end date=1/7/25

Results

2025W1=2  2025W2=2  2025W3=1  2025W4=1

[danextian · Accepted solution]

Hi @shawndempsey7 

You've provided your expected result but the solution depends on the actual data. We don't know what your tables look like. Are the weeks broken down into days or just a week start and end dates? Assuming that's the structure of your weeks table, please see the attached sample pbix

[V-yubandi-msft]

Hi @shawndempsey7 ,
As we haven’t heard back from you, we wanted to check if your issue has been resolved. If you still need any additional details, please let us know.

[Shubham_rai955]

Yes, Power BI can return all week numbers covered by employee date ranges using DAX and a supporting date table. The approach is to generate all dates between each employee's start and end dates, link them to a date table with a "YearWeek" column, and count week appearances.

Step-by-Step Solution

• Create a Date Table: Add a table with all dates for your analysis period. Include a column for YearWeek (e.g., 2025W1) using DAX with WEEKNUM:

  YearWeek = FORMAT([Date], "YYYY") & "W" & WEEKNUM([Date], 1)

• Generate Employee Active Dates: Use DAX or Power Query to create rows for every date in each employee's range, linking EmployeeID, Date, StartDate, EndDate.​

• Relate Employee-Date to Date Table: Join the generated date rows to the date table on the date field, so each active date inherits a YearWeek value.​

• Count by Week: Summarize (COUNTROWS or aggregation) by YearWeek to get counts per week. The result shows exactly how many employees are active during each week:

  EmployeeCountByWeek = SUMMARIZE( EmployeeActiveDates, [YearWeek], "Count", COUNTROWS(EmployeeActiveDates) )

Example Output

YearWeek EmployeeCount

2025W1	2 ​
2025W2	2 ​
2025W3	1 ​
2025W4	1 ​

With this method, you can analyze and visualize employee counts by week for any date ranges using only DAX and relationships.

[theov]

I would create a calendar table and do all there: https://youtu.be/Oq5WOmo94_Q?si=IDHOPH1crJGfGFoZ

[ryan_mayu · Accepted solution]

@shawndempsey7 

pls see if this is what you want

[shawndempsey7]

Thanks for the continued help...this certainly gets me closer. What I am truly stuck on is that I have a start and end date. I can convert those two dates into an associated week number, but I'm struggling to capture any weeks in between, i.e. start =January 6,2025 end= January 24, 2025. This will return 2025W1=1 and 2025W3=1, but I am losing that 2025W2 should also equal 1. Does this make sense?

[V-yubandi-msft]

Hi @shawndempsey7 ,

Thanks for the clarification. The reason you’re missing some weeks is because your setup only captures the start and end weeks. To get all the weeks in between, you’ll need to generate every week number that falls within each employee’s date range.

You can do this by building a calculated table or writing a DAX formula that expands all dates (or weeks) between the start and end dates, then groups them by week.

Hope this helps...

Regards,
Yugandhar.

[shawndempsey7]

Yes, this is what I'm hoping to acheive. Thank you!

[V-yubandi-msft]

Hi @shawndempsey7 ,

Thank you for your response and for staying engaged with the community. If possible, I kindly ask that you mark @ryan_mayu reply as the accepted solution this will make it easier for other members to find and benefit from the discussion.

Appreciate your valuable input, @ryan_mayu .

Best regards,
Yugandhar.

[danextian · Accepted solution]

Hi @shawndempsey7 

You've provided your expected result but the solution depends on the actual data. We don't know what your tables look like. Are the weeks broken down into days or just a week start and end dates? Assuming that's the structure of your weeks table, please see the attached sample pbix

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.  In that file, ensure that there is a Calendar table with a week column.

[shawndempsey7]

Thank you,

Unfortunately, I'm getting a "The date column must have unique values." message when I attempt to created a date table. Please see the sample data link below:

https://mydocs.toyota.com/:u:/r/personal/shawn_dempsey_toyota_com1/Documents/Sample%20to%20Share.pbi...

[Ashish_Mathur]

That takes me to a sign-in page.

[shawndempsey7]

Sorry...I'm new to the solution and can't figure out how to share outside of my organization. However, the data is very simple:

TM#	LWD Total	RWD Total	Absence Day Counts Lost Restricted Date	LWD End Date	RWD End Date
368	0	20	6/10/2024	6/10/2024	6/30/2024
9072	0	2	7/1/2024	7/1/2024	7/3/2024
428	0	12	3/20/2025	3/20/2025	4/1/2025
1286	0	30	4/1/2025	4/1/2025	5/1/2025
8777	0	31	5/1/2025	5/1/2025	6/1/2025
472	0	11	6/1/2025	6/1/2025	6/12/2025
1110	0	19	7/1/2025	7/1/2025	7/20/2025
231	0	2	7/30/2025	7/30/2025	8/1/2025
5490	0	31	8/1/2025	8/1/2025	9/1/2025
7929	0	30	9/1/2025	9/1/2025	10/1/2025
188	0	16	10/1/2025	10/1/2025	10/17/2025
5159	0	16	4/15/2024	4/15/2024	5/1/2024
8646	0	31	5/1/2024	5/1/2024	6/1/2024
9271	0	3	6/1/2024	6/1/2024	6/4/2024
644	0	1	3/31/2025	3/31/2025	4/1/2025
2458	0	30	4/1/2025	4/1/2025	5/1/2025
859	0	31	5/1/2025	5/1/2025	6/1/2025
1298	0	30	6/1/2025	6/1/2025	7/1/2025
9255	31	0	7/1/2025	8/1/2025	7/1/2025
6337	19	0	8/1/2025	8/20/2025	8/1/2025
9938	0	5	9/26/2024	9/26/2024	10/1/2024
3806	0	16	10/1/2024	10/1/2024	10/17/2024
2466	0	3	4/28/2025	4/28/2025	5/1/2025
5099	0	31	5/1/2025	5/1/2025	6/1/2025
6094	0	30	6/1/2025	6/1/2025	7/1/2025
4637	0	10	7/1/2025	7/1/2025	7/11/2025
900	1	19	9/1/2025	9/2/2025	9/20/2025

[Ashish_Mathur]

As requested, please also share a Calendar table with a week number column (since i do not know what the start and end of week is for you).  Also, for a few rows of the sample data, show the expected result.

[shawndempsey7]

Sorry...hope this clarifies:

TM#	LWD Total	RWD Total	Absence Day Counts Lost Restricted Date	LWD End Date	RWD End Date	Absense Week Start	LWD Week End	RWD Week End	Year-Week	Desired LWD Result	Desired RWD Result
3920	31	0	1/1/2024	2/1/2024	1/1/2024	1	5	1	2024-W1	2	3
4131	9	0	1/1/2024	1/10/2024	1/1/2024	1	2	1	2024-W2	2	3
7077	0	31	1/1/2024	1/1/2024	2/1/2024	1	1	5	2024-W3	1	3
339	0	31	1/1/2024	1/1/2024	2/1/2024	1	1	5	2024-W4	1	3
5777	0	31	1/1/2024	1/1/2024	2/1/2024	1	1	5	2024-W5	1	3

[Shahid12523]

Create a Date table with Date, WeekNum, and YearWeek (e.g., "2025W01").
Expand each employee’s date range using CALENDAR(StartDate, EndDate).
Join with the Date table to get YearWeek for each date.
Summarize by YearWeek to count how many employees are active per week.

[shawndempsey7]

Thank you,

Unfortunately, I'm getting a "The date column must have unique values." message when I attempt to created a date table.