Need help creating week number

[kcantor]

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?

[malguera]

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.

 

Thanks!

[Superdelucs]

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. 

[Greg_Deckler]

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

[fbrossard]

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/

[Anonymous]

Very helpful, thanks!

[Greg_Deckler]

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/

[kcantor · Accepted solution]

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]

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

[ShadiAyoub]

Try this:

Date.WeekOfYear(#date)

#date = The column that contains your date.