Forum Discussion
Return all week numbers in a range
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
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
17 Replies
Shubham_rai955Super User
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 EmployeeCount2025W1 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.
- Shahid12523
Community Champion
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.Thank you,
Unfortunately, I'm getting a "The date column must have unique values." message when I attempt to created a date table.
- 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.
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:
- Ashish_Mathur
That takes me to a sign-in page.
- danextian
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
- ryan_mayu
Yes, this is what I'm hoping to acheive. Thank you!
V-yubandi-msftCommunity Support
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.
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.
theovAdvocate III
I would create a calendar table and do all there: https://youtu.be/Oq5WOmo94_Q?si=IDHOPH1crJGfGFoZ
- 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.
