Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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_CH
Solved! Go to Solution.
@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
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
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.
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"))
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
Thanks a lot!
You absolute legend!!
@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
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
Thank you, Antionio! That did it 🙂
Hi amitchandak
Your solution worked. Thanks a lot.
Have a nice day.
Cheers
Mike_CH
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
23 | |
21 | |
20 | |
13 | |
12 |
User | Count |
---|---|
43 | |
31 | |
24 | |
23 | |
22 |