Forum Discussion
Year Week column
Hi
I am struggling to get week year working. I have it year week so i can put it in the right order in tables but it doesnt get it right.
This is what I currently use
WeekYr = VAR _year = RIGHT(YEAR([DATE_TIME]), 2) // Extract the last two digits of the year VAR _week = WEEKNUM([DATE_TIME], 2) // Extract the week number of the year with Monday as the first day of the week // Adjust the week number if it is 53 to be included in Week 1 of the next year VAR _adjustedWeek = IF(_week = 53, 1, _week) // Format the week with leading zeros if needed VAR _formattedWeek = FORMAT(_adjustedWeek, "00") // Combine the formatted week and year RETURN _year & "-" & _formattedWeek
all is fine except 30/12/2024 which shows as 24-01 anoyingly at the start of the year!
any ideas on how to fix?
Ah yes — the classic "week spillover" issue at the end of the year. This happens because WEEKNUM([DATE_TIME], 2) always uses January 1st as the start of the year, regardless of the ISO week standard.
The result is that dates like 30/12/2024 get assigned Week 1 of 2025, but your formula still pulls the year from the DATE_TIME itself, giving you 24-01, which is misleading.
To fix this and make sure week/year pairs follow the actual ISO 8601 standard (where week 1 is the week with the first Thursday of the year, and weeks can start in the previous or next year), use ISO week numbers and ISO week years.
Here's a corrected DAX formula:
WeekYr = VAR _date = [DATE_TIME] VAR _isoYear = YEAR(_date + (4 - WEEKDAY(_date, 2))) VAR _isoWeek = WEEKNUM(_date, 21) // ISO week numbering, where week starts on Monday RETURN FORMAT(_isoYear, "00") & "-" & FORMAT(_isoWeek, "00")
Why it works:
WEEKNUM([DATE_TIME], 21) gives you the ISO week number, which avoids the weird 53-week wrap.To get the correct ISO year, I use the trick:
VAR _isoYear = YEAR(_date + (4 - WEEKDAY(_date, 2)))This aligns with the ISO definition that Week 1 contains the first Thursday of the year.
Result for 30-Dec-2024:
This will now correctly give you 25-01 instead of 24-01, because that week belongs to the first week of 2025 under ISO rules.Please mark this post as solution if it helps you. Appreciate kudos.
Hi Southbound,
Thank you for reaching out to the Microsoft fabric community forum. Thank you FarhanJeelani, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.Dax Measure For WeekYr:
WeekYr =VAR WeekDay = WEEKDAY([Date], 2) -- Monday = 1, Sunday = 7
VAR AdjustedDate = [Date] + (4 - WeekDay) -- Shift to Thursday
VAR ISO_Year = YEAR(AdjustedDate)
VAR ISO_Week = WEEKNUM(AdjustedDate, 2) -- ISO-style week number
RETURN FORMAT(ISO_Year, "00") & "-" & FORMAT(ISO_Week, "00")
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.
6 Replies
to answer my own question i think this is the solution
WeekYr =
VAR _year = RIGHT(YEAR([Date]), 2) // Extract the last two digits of the year
VAR _week = WEEKNUM([Date], 2) // Extract the week number of the year with Monday as the first day of the week
// Format the week with leading zeros if needed
VAR _formattedWeek = FORMAT(_week, "00")
// Combine the formatted week and year
RETURN _year& "-" &_formattedWeek
Ah yes — the classic "week spillover" issue at the end of the year. This happens because WEEKNUM([DATE_TIME], 2) always uses January 1st as the start of the year, regardless of the ISO week standard.
The result is that dates like 30/12/2024 get assigned Week 1 of 2025, but your formula still pulls the year from the DATE_TIME itself, giving you 24-01, which is misleading.
To fix this and make sure week/year pairs follow the actual ISO 8601 standard (where week 1 is the week with the first Thursday of the year, and weeks can start in the previous or next year), use ISO week numbers and ISO week years.
Here's a corrected DAX formula:
WeekYr = VAR _date = [DATE_TIME] VAR _isoYear = YEAR(_date + (4 - WEEKDAY(_date, 2))) VAR _isoWeek = WEEKNUM(_date, 21) // ISO week numbering, where week starts on Monday RETURN FORMAT(_isoYear, "00") & "-" & FORMAT(_isoWeek, "00")
Why it works:
WEEKNUM([DATE_TIME], 21) gives you the ISO week number, which avoids the weird 53-week wrap.To get the correct ISO year, I use the trick:
VAR _isoYear = YEAR(_date + (4 - WEEKDAY(_date, 2)))This aligns with the ISO definition that Week 1 contains the first Thursday of the year.
Result for 30-Dec-2024:
This will now correctly give you 25-01 instead of 24-01, because that week belongs to the first week of 2025 under ISO rules.Please mark this post as solution if it helps you. Appreciate kudos.
Hi Southbound,
Thank you for reaching out to the Microsoft fabric community forum. Thank you FarhanJeelani, for your inputs on this issue.
After thoroughly reviewing the details you provided, I was able to reproduce the scenario, and it worked on my end. I have used it as sample data on my end and successfully implemented it.Dax Measure For WeekYr:
WeekYr =VAR WeekDay = WEEKDAY([Date], 2) -- Monday = 1, Sunday = 7
VAR AdjustedDate = [Date] + (4 - WeekDay) -- Shift to Thursday
VAR ISO_Year = YEAR(AdjustedDate)
VAR ISO_Week = WEEKNUM(AdjustedDate, 2) -- ISO-style week number
RETURN FORMAT(ISO_Year, "00") & "-" & FORMAT(ISO_Week, "00")
I am also including .pbix file for your better understanding, please have a look into it:
If this post helps, then please give us ‘Kudos’ and consider Accept it as a solution to help the other members find it more quickly.
Thank you for using Microsoft Community Forum.Hi Southbound,
May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.
Thank you.
Hi Southbound,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.
