Forum Discussion
Return all week numbers in a range
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
Super User
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-msftCommunity Support
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.