Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all. I need to create a Week Number column in a date table, but the WEEKNUM function doesn't produce quite the right results.
For the purposes of this variable, weeks start on Sunday. The part where it gets tricky is around the end/beginning of a year.
If the last Sunday of the year falls on December 29, 30, or 31, then I need the following set of 7 days (including the last Sunday in December) to be assigned a week number of 1. If the last Sunday of December falls on December 25, 26, 27, or 28 I need the following set of 7 days (including the last Sunday of December) to be assigned to week 52 or 53 or 54 or whatever is appropiate that year. The end result is that fo some years, there will be some dates in December assigned to week 1, and in other years there will be some days in January that are assigned to week 52 or 53 or 54.
Here are a couple examples of what the output should be:
Day | Date | Week Number |
Saturday | 12/29/2018 | 52 |
Sunday | 12/30/2018 | 1 |
Monday | 12/31/2018 | 1 |
Tuesday | 1/1/2019 | 1 |
Wednesday | 1/2/2019 | 1 |
Thursday | 1/3/2019 | 1 |
Friday | 1/4/2019 | 1 |
Saturday | 1/5/2019 | 1 |
Sunday | 1/6/2019 | 2 |
Day | Date | Week Number |
Saturday | 12/26/2015 | 52 |
Sunday | 12/27/2015 | 53 |
Monday | 12/28/2015 | 53 |
Tuesday | 12/29/2015 | 53 |
Wednesday | 12/30/2015 | 53 |
Thursday | 12/31/2015 | 53 |
Friday | 1/1/2016 | 53 |
Saturday | 1/2/2016 | 53 |
Sunday | 1/3/2016 | 1 |
I appreciate any help!
Solved! Go to Solution.
try adding a calculated column with this formula
=WEEKNUM( [Date] + 1, 21 )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
@Anonymous
This will answer your question:
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
@Anonymous
This will answer your question:
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
try adding a calculated column with this formula
=WEEKNUM( [Date] + 1, 21 )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @LivioLanzo ,
Thanks for the solution, this has helped me as well.
I would like to know the logic behind 21, as i could see while typing the formula, we have the option of only 1 (sunday) and 2(Monday).
Thanks in advance.
Regards,
Pavan
Wow, that was remarkably simple! Thanks so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |