calculate the week of the year between 1 and 52

[vsteinly]

So I have a date table and added a column for week number.  Using WEEKDAY ( [Date] ) in my table some years I end up with 53 weeks.  What should I add to my DAX to code that out?

[Greg_Deckler]

There is an undocumented value for the second WEEKNUM parameter, 21. Using that value will force 52 weeks.

[JP-Ronse]

Hi,

True but confusing and can return wrong values.

WEEKNUM(xx,21) is the ISO week numbering but it still counts up to 53. If the first of January falls in week 52 and you pivot on week, the values of week January 1st and December 31th will be added together.

[Greg_Deckler]

Well, I'm not sure I would say it returns "wrong" values. It returns the values as specified by ISO. I've never seen it actually return a 53 when using 21. For example, if you create a table of dates for 2017:

2017 = CALENDAR(DATE(2017,1,1),DATE(2017,12,31))

And you create a column:

WeekNum1 = WEEKNUM([Date],1)

January 1st - January 7th are week 1, Dec 24th - 30th are 52 and Dec 31 is 53.

If you create a column:

WeekNum = WEEKNUM([Date],2)

Jan 1st is week 1, Jan 2nd - 8th are week 2 and Dec 25 - 31 are week 53. 

With 21, January 1 is week 52 (of 2016) and Dec 25th - 31 are 52 of this year.

I suppose it is possible as specified by: https://en.wikipedia.org/wiki/ISO_8601

But, there again, I would hestitate to call it "wrong", it is correctly following the ISO standard. For example, if you use 2010, you can get a 53'rd week, but that value is correct given the ISO standard, which essentially counts Thursday's for some strange, mysterious reason. 

I guess it boils down to what you are expecting. If you really want only 52 weeks and only 52 weeks you will need to define a system by which you get that. That could be just converting any 53 into a 52 but, again, what standard are you using to define this sort of thing? Is it absolutely necessary or did you just not realize that years can actually technically have a 53rd week and not 52 and so it bugs you because you have always been told that years have 52 weeks?

[JP-Ronse]

Hi,

I didn't explain it completely. The function returns the correct values but the results in the pivot can be wrong. Say I want to sum "events" per week. The function return 52 for the 01/01/2017 and 52 for 12/25/2017. So that would sum both weeks although the peiods are not related.

[Greg_Deckler]

Right, so in that case, if you want your pivot correct, include year.

[MarcelBeug · Accepted solution]

My suggestion would be:

Weeknum = 1 + mod(weeknum(Query2[Date]) - 1,52)

Note that leap years, starting on a Saturday, have also a week number 54 (e.g. Dec 31, 2028), which will become 2 in this formula.

I think ISO week numbers are better explained in: https://en.wikipedia.org/wiki/ISO_week_date

[vsteinly]

So this is giving me the 1 through 52 weeks.  Thanks for your input.

[vsteinly]

I work at a water utility and we are looking at rainfall vs water production.  So I want to measure monthly and weekly totals.  I have this in Excel and basically told it if there was 53 weeks to make it the 1st week.  So it looks like that is what I will need to do in power bi.  Thanks for taking your time to look at this though.

[ccakjcrx]

Hey @vsteinly!

If you have a column in one of your tables that contains the date, you can use the following DAX to create a calculated column comprised of corresponding week numbers:

ClmWeekNo = 
WEEKNUM(Sheet1[Date])

Here is a screenshot of my table with the new column:

 

Click HERE for documentation regarding this expression.

Hope this helps!