Year Week column

Southbound , 

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.