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
Mike_CH
Frequent Visitor

Function Calendar Wrong WeekNum

Hi there,

I have a date table with the following DAX-Code: Dates = CALENDAR(DATE(2018,01,01),DATE(2030,12,31))

 

According to the documentation, the WeekNum function works like this:
"By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year. However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition."

 

For the year 2021 happens exactly this case. There are 1.1. - 3.1. in the week 53 of the year 2020. But the the WEEKNUM-Functions shows this three days as the week no. 1 of the year 2021. The result, the WeekNum numbers don't match with the no. in europe.

 

How can I fix mismatch?

 

Thanks for help

 

Cheers

Mike_CHMismatch.JPG

 

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Mike_CH , Try these column and see if last column give you desired output

Create a week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday

or

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday

 

Week Year = year([Week Start date])

 

First Week start of year = minx(filter('Date', [Week Year] =earlier([Week Year])),[Week Start date])

Week of Year = Quotient( datediff([First Week start of year],[Date],Day),7)+1

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

PeJo
Advocate I
Advocate I

You should use WEEKNUM ( 'Date'[Date], 21 )

Returntype 21 = ISO 8601 Week or the European weeknumbering system, no need for custom formulas or functions.

You could also read more about the Weeknum function here: WEEKNUM – DAX Guide

View solution in original post

10 REPLIES 10
subha17
Regular Visitor

hi Michelle, did you get the solution for your problem? i too face the same issue. Wk52 2021 has to show last 4 days of Dec '21 and first 3 days of Jan'22. but it shows first days of Jan'21. Pls share solution if you found one.

Anonymous
Not applicable

Hi everyone, 

 

I have a similar issue. My week needs to start on sunday and the column "calendarweeksunday" is sources from a snowflake database. I tried to create the yearweek which basically works but at the end of the year when it switches over to Jan 1st it will count as KW53 in year 2021 but it should be KW53 in 2020.

My current DAX is:
YEARWEEK = combinevalues("-w-", 'V_DIM_DATE (2)'[YEAR], format('V_DIM_DATE (2)'[CALENDARWEEKSUNDAY], "00"))
Michella_0-1638888188065.png

 

PeJo
Advocate I
Advocate I

You should use WEEKNUM ( 'Date'[Date], 21 )

Returntype 21 = ISO 8601 Week or the European weeknumbering system, no need for custom formulas or functions.

You could also read more about the Weeknum function here: WEEKNUM – DAX Guide

Anonymous
Not applicable

Thanks a lot!

Anonymous
Not applicable

You absolute legend!!

amitchandak
Super User
Super User

@Mike_CH , Try these column and see if last column give you desired output

Create a week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday

or

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday

 

Week Year = year([Week Start date])

 

First Week start of year = minx(filter('Date', [Week Year] =earlier([Week Year])),[Week Start date])

Week of Year = Quotient( datediff([First Week start of year],[Date],Day),7)+1

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Hi Amitchandak, 

 

I tried your solution, but did not get it 100% right. My calender table starts with 2019 Oct (our fisical year goes from Oct-Sept). 

So when I used your method, first week in October becomes week 1. But it restarts as week 1 in January 2020, however not on the right day...

With your method Week 1 2020 starts on the 6th of Jan, but should be 30th of Dec 2019. 

 

Do you know how I could solve this? The incorrect weeks in 2019 are not important, since our reporting started  during 2020, however, I need the weeks to be correct from 2020 and forward. 

 

Thank you for your help!

 

/Vanessa

Hi Vanessa,

 

you can substract 1 from week number if first week of year have less than 4 days.

 

DayOfWeek = WEEKDAY(DATE(YEAR([Date])1,1,),1)  // for calendars starting week on sunday
DayOfWeek = WEEKDAY(DATE(YEAR([Date])1,1,),2)  // for calendars starting week on monday

WeekNumber = WEEKNUM([Date]) - IF(DayOfWeek > 4, 1, 0) 

Regards.

/Antonio

 

 

 

Anonymous
Not applicable

Thank you, Antionio! That did it 🙂 

Hi amitchandak

 

Your solution worked. Thanks a lot.

 

Have a nice day.

Cheers
Mike_CH

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.