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

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?

 



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

Proud to be a Datanaut!




1 ACCEPTED SOLUTION

Accepted Solutions
kcantor Super Contributor
Super Contributor

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.

 



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

Proud to be a Datanaut!




View solution in original post

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


I have book! Learn Power BI from Packt


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/

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

kcantor Super Contributor
Super Contributor

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.

 



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

Proud to be a Datanaut!




View solution in original post

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. 

Anonymous
Not applicable

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!




Highlighted
ShadiAyoub Occasional Visitor
Occasional Visitor

Re: Need help creating week number

Try this:

 

Date.WeekOfYear(#date)

 

#date = The column that contains your date.

 

 

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)