cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jcpewittyancey Regular Visitor
Regular Visitor

Creating a custom Week Number variable in a date table

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!

1 ACCEPTED SOLUTION

Accepted Solutions
LivioLanzo Super Contributor
Super Contributor

Re: Creating a custom Week Number variable in a date table

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!  

View solution in original post

2 REPLIES 2
LivioLanzo Super Contributor
Super Contributor

Re: Creating a custom Week Number variable in a date table

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!  

View solution in original post

jcpewittyancey Regular Visitor
Regular Visitor

Re: Creating a custom Week Number variable in a date table

Wow, that was remarkably simple! Thanks so much!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 139 members 1,446 guests
Please welcome our newest community members: