cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jwi1
Post Patron
Post Patron

Calender with weeknumbers according to ISO8601

Hi all,

Below is a calender, which I use in my report.

However, the weeknumber is not according to ISO8601.

I know there is a possibility with the formula WEEKNUM: WEEKNUM([Date], 21)

the 21 gives me the weeknumber according to ISO8601.

But how must I change the tellow marked line below to get this done?

Hope someone can help me out with this!

Johncalender.PNG

1 ACCEPTED SOLUTION

I will take you on your word that weeknum (21) gives what you need. Weeknum is a DAX function, not a PQ function. So don't add the weeknum in PQ, but instead add it as a calculated column in DAX. I would normally not advise this, but based on your post, it would seem that it could work. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

3 REPLIES 3
jwi1
Post Patron
Post Patron

Dear Matt,

 

Highly appreciated, followed your advise, all fine now.

 

Have a good day!

 

John

OwenAuger
Super User
Super User

I notice @MattAllington just replied as well while I was investigating this.

 

After lookup up the ISO 8601 definition, I see that an ISO week runs from Monday to Sunday, and the year a given week falls in is the year containing the Thursday of that week.

 

Here is a formula that should work in Power Query in the context of the code you posted:

 

= Table.AddColumn(#"Inserted Month", "Week of Year",
  each let
    DayOfWeek = Date.DayOfWeek([Date],Day.Monday),
    ThursdayDate = Date.AddDays([Date],3-DayOfWeek),
    ThursdayDayOfYear = Date.DayOfYear(ThursdayDate)
  in
    Number.IntegerDivide(6+ThursdayDayOfYear, 7 ),
  Int64.Type
)

 

It basically says:

  1. For the current date, find the date of the Thursday within the same week (ThursdayDate).
  2. Get the day-of-year for that Thursday (ThursdayDayOfYear).
  3. Then do integer division of ( ThursdayDayOfYear + 6 ) by 7.

This works because, for example, if the Thursday of a given week falls within days 1 to 7 of the year, the division in step 3 ranges between 7 / 7 to 13 / 7, all of which return 1, which is correct since that must be the first Thursday of the year. Subsequent weeks will increment accordingly.

 

Regards,

Owen


Owen Auger

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

My Blog
Connect on Twitter
Connect on LinkedIn

I will take you on your word that weeknum (21) gives what you need. Weeknum is a DAX function, not a PQ function. So don't add the weeknum in PQ, but instead add it as a calculated column in DAX. I would normally not advise this, but based on your post, it would seem that it could work. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!