Week Number Not Returning Week 1 In New Year

Hii L1102 

Your week is starting at Week 2 because WEEKNUM(...,14) follows ISO week rules, where Week 1 is defined by the first Thursday of the calendar year. Since your fiscal year also begins on a Thursday, the last days of the old year and the first days of the new year fall into the same ISO week, so Power BI doesn’t reset to Week 1. You need to calculate the week based on your fiscal start date, not ISO rules  e.g., 1 + INT(([Date] – FiscalStartDate) / 7)  to force Week 1 to restart correctly each year.

Week Fiscal =
VAR StartOfYear = DATE([Year], 5, 1)      
RETURN
1 + INT( ( [Date] - StartOfYear ) / 7 )

Hello L1102,

The issue lies in this part of the formula:

CalendarWeekNum + (52 - WeekNumber) + 1

1 + (52 - 52) + 1 = 1 + 0 + 1 = 2

This means the first week of the new fiscal year is incorrectly returned as Week 2 instead of Week 1.

Let me show another possible approach:

FiscalWeek =
VAR CurrentDate = 'Sobeys Promo Calendar'[Promo Week Start Date].[Date]
VAR YearStartDate = DATE('Sobeys Promo Calendar'[Year], 5, 1)
VAR CalendarWeekNum = WEEKNUM(CurrentDate, 14)
VAR WeekNumber = WEEKNUM(YearStartDate, 14)
RETURN
MOD(CalendarWeekNum - WeekNumber + 52, 52) + 1

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

Hi L1102 

Download example PBIX file with the data/code below

Given that your data has a date column and the fiscal year for that date, you can get the fiscal week with this

Fiscal Week = INT(([Date] - DATE([Year],5,1))/7) + 1

If your fiscal year starts on 1 May then Apr 30 2025 is actually the start of Week 53. 

There are 365 days in a year (366 in a leap year) so 365 / 7 is 52.14.  There are always 52 weeks plus another day, or two days in leap years.

If you really want to cap the week number to 52 then you can use this

Fiscal Week = 

VAR _weeknum = INT(([Date] - DATE([Year],5,1))/7) + 1

RETURN

IF(_weeknum > 52, 52, _weeknum)

Regards

Phil

Hi L1102,

I hope you are doing well and Welcome to Microsoft Fabric Community ☺️❤️

So Looking at your issue i noticed the issue is likely with how your DAX formula handles the transition between fiscal years when your fiscal week starts on a Thursday...So her is a couple of Approaches:

First Approach: Using WEEKNUM with Custom Start Day

Fiscal Week = 
VAR CurrentDate = [Your Date Column]
VAR YearStart = DATE(YEAR(CurrentDate), 1, 1)
VAR ThursdayOffset = MOD(5 - WEEKDAY(YearStart, 2) + 7, 7)  // 5 represents Thursday in ISO (1=Mon, 7=Sun)
VAR FirstThursday = YearStart + ThursdayOffset
RETURN
IF(
    CurrentDate >= FirstThursday,
    WEEKNUM(CurrentDate, 2) - WEEKNUM(FirstThursday, 2) + 1,
    // Handle dates before first Thursday (belong to previous fiscal year)
    52 - WEEKNUM(FirstThursday, 2) + WEEKNUM(CurrentDate, 2) + 1
)

Second Approach: More Robust Fiscal Week Calculation

Fiscal Week = 
VAR CurrentDate = [Your Date Column]
VAR CurrentYear = YEAR(CurrentDate)
VAR Jan1 = DATE(CurrentYear, 1, 1)
VAR DayOfWeek = WEEKDAY(Jan1, 2)  // Monday = 1, Sunday = 7

// Find first Thursday of the year
VAR FirstThursday = 
    SWITCH(
        TRUE(),
        DayOfWeek = 1, Jan1 + 3,  // Monday -> Thursday is +3
        DayOfWeek = 2, Jan1 + 2,   // Tuesday -> Thursday is +2
        DayOfWeek = 3, Jan1 + 1,   // Wednesday -> Thursday is +1
        DayOfWeek = 4, Jan1,       // Thursday
        DayOfWeek = 5, Jan1 + 6,   // Friday -> Next Thursday is +6
        DayOfWeek = 6, Jan1 + 5,   // Saturday -> Next Thursday is +5
        DayOfWeek = 7, Jan1 + 4    // Sunday -> Next Thursday is +4
    )

RETURN
IF(
    CurrentDate < FirstThursday,
    // This date belongs to previous fiscal year's last weeks
    WEEKNUM(CurrentDate, 2) + (52 - WEEKNUM(FirstThursday - 1, 2)),
    // Normal case: calculate weeks from first Thursday
    WEEKNUM(CurrentDate, 2) - WEEKNUM(FirstThursday, 2) + 1
)

Bonus Approach: Using Date Difference (I Recommend it)

Fiscal Week = 
VAR CurrentDate = [Your Date Column]
VAR CurrentYear = YEAR(CurrentDate)
VAR Jan1 = DATE(CurrentYear, 1, 1)
VAR DayOfWeekJan1 = WEEKDAY(Jan1, 2)

// Calculate first Thursday
VAR FirstThursday = Jan1 + MOD(11 - DayOfWeekJan1, 7)

// Calculate fiscal year start
VAR FiscalYearStart = 
    IF(
        CurrentDate < FirstThursday,
        DATE(CurrentYear - 1, 1, 1) + MOD(11 - WEEKDAY(DATE(CurrentYear - 1, 1, 1), 2), 7),
        FirstThursday
    )

RETURN
DIVIDE(CurrentDate - FiscalYearStart, 7, 0) + 1

Hi,
To fix it, compute:

The first Thursday of the year

The week number based on that first Thursday

FirstFiscalThursday =
VAR YearStart = DATE( YEAR('Date'[Date]), 1, 1 )
VAR DayOfWeek = WEEKDAY( YearStart, 2 ) -- Monday = 1 … Sunday = 7
VAR Offset = MOD( 4 - DayOfWeek, 7 ) -- 4 = Thursday
RETURN
YearStart + Offset

FiscalWeek =
1 +
QUOTIENT(
DATEDIFF( [FirstFiscalThursday], 'Date'[Date], DAY ),
7
)

These are the exact Microsoft Learn documentation pages for the functions used:

WEEKDAY function (DAX) 

DATEDIFF function (DAX) 

QUOTIENT function (DAX) 

DATE function (DAX) 

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Regards,
Rufyda Rahma | MIE