Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
kcantor
Community Champion
Community Champion

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 Super User!




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

 





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

Proud to be a Super User!




View solution in original post

9 REPLIES 9
malguera
Frequent Visitor

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!




Superdelucs
Frequent Visitor

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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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
Not applicable

Very helpful, thanks!

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/

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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 Super User!




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

Try this:

 

Date.WeekOfYear(#date)

 

#date = The column that contains your date.

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.