cancel
Showing results for
Did you mean:
Highlighted
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
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 )`

Proud to be a Datanaut!

2 REPLIES 2
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 )`

Proud to be a Datanaut!

Regular Visitor

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

Wow, that was remarkably simple! Thanks so much!

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### Win Power BI Swag with Community Kudopalooza!

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

Top Kudoed Authors
Users Online
Currently online: 139 members 1,446 guests
Recent signins: