cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Need help creating week number

I have a project that I am working on with a simplified calendar used as a DimDate. This calendar has: Date, Year, Month, Quarter, Month Name, Day of Week, Week Number, and YearMonth. What it does not have is ISO Week number. The week number that it has is calculated using =WEEKNUM,[date],2 and those numbers that do not match for sales amounts. Is there a simple way to create an ISO week number so that I can use this project for the next 10 days until my calendar frees up for me to rework the entire thing?

Is it =WEEKNUM, [Date], 21?

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Need help creating week number

yes. I started out with serial number 2 as that is what I was told the program used. It turns out that our Navision uses ISO. I had created week numbers using serial number 2 but was able to correct the informaiton chaning it wo week serial number 21.

Happy to know I was not the only one using 2.

 

9 REPLIES 9
Super User
Super User

Re: Need help creating week number

My understanding of the ISO week number system is that it begins in Monday and ends on Sunday, days 1-7. Do you want just the ISO week number? That would be:

 

= WEEKNUM([Date],2)

 

If you want the full ISO date as specified here: https://en.wikipedia.org/wiki/ISO_week_date

 

That would be (compact form):

 

= YEAR([Date]) & "W" & WEEKNUM([Date],2) & WEEKDAY([Date],2)

 

See WEEKNUM reference here: https://support.office.com/en-US/article/WEEKNUM-Function-DAX-e636ef36-180a-4e2d-a29b-8f549c258da0


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


fbrossard Member
Member

Re: Need help creating week number

In DAX, you can create calculated those columns :
  • [Iso Week] = WEEKNUM([Date];21)  
  • [Iso Year] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year]))
  • [Iso Year Week] = IF( AND(WEEKNUM([Date];21) < 5;WEEKNUM([Date];2) > 50);[Year]+1;IF(AND(WEEKNUM([Date];21) > 50;WEEKNUM([Date];2) < 5);[Year]-1;[Year])) & " week " & FORMAT(WEEKNUM([Date];21);"00")
See http://fbro.wordpress.com/2013/02/07/powerpivot-excel2013-table-temps-universelle-pour-vos-pocs/
Super User
Super User

Re: Need help creating week number

Wild, learned something, I guess the documentation on WEEKNUM is incomplete, I couldn't find 21 as a valid argument anywhere for WEEKNUM but tried it and it works, and it does make a difference.

 

https://support.office.com/en-US/article/WEEKNUM-Function-DAX-e636ef36-180a-4e2d-a29b-8f549c258da0

 

But, found confirmation from Brueckl

 

http://blog.gbrueckl.at/2012/04/iso-8601-week-in-dax/

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Need help creating week number

yes. I started out with serial number 2 as that is what I was told the program used. It turns out that our Navision uses ISO. I had created week numbers using serial number 2 but was able to correct the informaiton chaning it wo week serial number 21.

Happy to know I was not the only one using 2.

 

chadhanitin Frequent Visitor
Frequent Visitor

Re: Need help creating week number

I added WEEKNUM([createdAt],2) but kept on getting error - "Expression error: The name 'WEEKNUM' wasn't recognized.  Make sure it's spelled correctly"

 

I am connecting to mysql database .. 

Superdelucs Frequent Visitor
Frequent Visitor

Re: Need help creating week number

Is there a solution for this problem? I am also encountering this issue. 

 

The problem is that I don't need the week of the year, but the iso-week for my reports. This currently doesn't seem te be an option in Power BI. 

JmvSteenbergen Frequent Visitor
Frequent Visitor

Re: Need help creating week number

Very helpful, thanks!

malguera Frequent Visitor
Frequent Visitor

Re: Need help creating week number

Hi Team,

Need some help here.

The default WEEKNUM function provides week number from the start of the year and runs 1 to 53 or 54.

 

If I need to show week number within the quarter, running 1 to 13 or 14 within each qtr, how should I do so?

 

I.e.

 

WeekOfQtr.PNG 

Thanks!




ShadiAyoub Occasional Visitor
Occasional Visitor

Re: Need help creating week number

Try this:

 

Date.WeekOfYear(#date)

 

#date = The column that contains your date.