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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
android1
Post Patron
Post Patron

WeekNum out by a few

Hi,

 

Any idea why the column Week No = WEEKNUM(AVS[ServiceDate]) is wrong by 1 or 2 weeks?

For Week 29 of this year, 18th July - 24th July its returning 30 for 18th-23rd & 31 for the 24th.WeekNum.jpg

 

 

1 ACCEPTED SOLUTION

It looks like Power BI counts the first week of the year starting on 1/1/2016, rather than including it in part of the last week of the prior year.

 

WEEKNUM.PNG

 

That goes against many standard calendars, specifically the ISO definition.

 

I'm not sure why MS uses a different system, but knowing when they start, you could subtract one from your formula and get the result you wanted.

 

This is also the default behavior in Excel, unless you use =WEEKNUM(DateCell, 21). In Power BI, there is no option for this. You can vote on the idea here to make that happen.

 

EDIT: other posters mentioned after this was accepted as a solution that you can use WEEKNUM([DateColumn], 21) in Power BI, it's just not documented and doesn't show up as a standard option while writing the formula. Thanks for pointing this out!

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

You need to realize that there are many different ways of measuring the week num of the year.  ISO, standard calendar, 1st full week starting on Sat, Sun or Mon, or different Fiscal calendars starting at other times throughout the year.  I personally think the ISO standard is the easiest to deal with, and in excel can be used either with =WEEKNUM([Date],21) or =ISOWEEK([Date]). 

 

In PowerBI, There are two ways to get the ISO Week:

 

  1. Using a DAX query:  (it's a nice undocumented option that still works the same as Excel.)
     ISO Week = WEEKNUM([Date],21)
     
  2. Using M in the Query Editor: 
    ISO Week = Number.RoundDown((Date.DayOfYear(Date.From(Duration.Days(([Date]-Date.From(2))/7)*7+5))+6)/7)
Anonymous
Not applicable

I'm having a hard time understanding from your question which date you feel is getting back a bad weeknum.  I can not repro this issue.   Are you being thrown off by the Leap Year in 2016?

Hi,

 

Isn't 18th - 24th July 2016 Week Number 29? I was expecting WeekNum to return 29 for these dates. Instead it returns 30 & 31.

Anonymous
Not applicable

Weeks start on Sunday, and July 17 starts the 30th week.   Note that Jan 1 - Jan 2 is considered "Week 1", with Week 2 starting Jan 3.

Hi,

 

This returns the correct WeekNum -> Week No = WEEKNUM(AVS[ServiceDate],21)

 

Thanks for the replies.

It looks like Power BI counts the first week of the year starting on 1/1/2016, rather than including it in part of the last week of the prior year.

 

WEEKNUM.PNG

 

That goes against many standard calendars, specifically the ISO definition.

 

I'm not sure why MS uses a different system, but knowing when they start, you could subtract one from your formula and get the result you wanted.

 

This is also the default behavior in Excel, unless you use =WEEKNUM(DateCell, 21). In Power BI, there is no option for this. You can vote on the idea here to make that happen.

 

EDIT: other posters mentioned after this was accepted as a solution that you can use WEEKNUM([DateColumn], 21) in Power BI, it's just not documented and doesn't show up as a standard option while writing the formula. Thanks for pointing this out!

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.