topic Re: Week Number Not Returning Week 1 In New Year in DAX Commands and Tips

Week Number Not Returning Week 1 In New Year

L1102 — Fri, 28 Nov 2025 15:06:32 GMT

Hello,

I'm new to Dax (2-3 months in), and hoping someone can help identify where I'm off in my Dax formula. I'm trying to return my week numbers off a custome fiscal calendar where the start date is on a Thursday. I have the code below which starts off perfectly with the oldest year, but when it transitions to the new year it starts at week 2. I'm not sure how to fix this.

Re: Week Number Not Returning Week 1 In New Year

rohit1991 — Fri, 28 Nov 2025 15:16:33 GMT

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 )

Re: Week Number Not Returning Week 1 In New Year

Zanqueta — Fri, 28 Nov 2025 15:32:28 GMT

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 🌀.

Re: Week Number Not Returning Week 1 In New Year

L1102 — Fri, 28 Nov 2025 16:24:59 GMT

Thanks so much Zanqueta, but unfortunetly this solution didn't seem to work.

Re: Week Number Not Returning Week 1 In New Year

L1102 — Fri, 28 Nov 2025 16:33:40 GMT

Hi rohit1991

This didn't seem to work, not sure if i entered the correct [date] my start date is subtracting from.

Re: Week Number Not Returning Week 1 In New Year

PhilipTreacy — Mon, 01 Dec 2025 02:06:07 GMT

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

Re: Week Number Not Returning Week 1 In New Year

Ahmed-Elfeel — Mon, 01 Dec 2025 02:12:29 GMT

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

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Week Number Not Returning Week 1 In New Year

L1102 — Mon, 01 Dec 2025 15:45:26 GMT

Hi PhilipTreacy This didn't seem to work for me. For whatever reaseon when I use the INT funstion it gives me a - week number. This may be due to how my date and year is being calculated. My date is a min/max so I can get my calendar to start on the Thursday, and my fiscal year is a EDATE so I can line up the months in the proper year.

Re: Week Number Not Returning Week 1 In New Year

L1102 — Mon, 01 Dec 2025 16:32:30 GMT

Hi Ahmed-Elfeel,

Thank you so much for your help, it's greatly appriciated. I'm starting to think this is all an issue due to the fact that my calendar date is established by a min/max, and my fiscal year is established by an EDATE. I think this is might be what's throwing everything off.

First Approach: Unfortunetly this didn't work, the return that I got was below, and you can see when my fiscal year convert over to 2026 I'm at week 18.

Second Approach: This gets me real close when I adjust my fiscal calendar to start in May, however something isn't calculating correctly with week 52 of prior year and week 1 of the new year. It doesn'e seem to give me a 7 day count in the week.

Bonus Approach: This one also get's me close when I adjust for a May fical start date, but like approach 2 something is happening with the transition from week 52 to week 1 of the new year.

Re: Week Number Not Returning Week 1 In New Year

PhilipTreacy — Tue, 02 Dec 2025 00:24:24 GMT

Hi L1102

Download example PBIX file with the code/data shown below

What is pertinent is the first date of the fiscal year which I can see from your calculations is 1 May. It just happens to be a Thursday in 2024.

I can see that you are creating Sobeys Promo Calendar using MIN and MAX dates from Time_Table.

Though I am not sure what data is in Time_Table, so I have created a table called Sobeys Promo Calendar using some dummy data for Time_Table

Sobeys Promo Calendar = CALENDAR(MIN('Time_Table'[Week Start Date]), MAX('Time_Table'[Week End Date]))

I can now create the (Fiscal) Year column using this

Year = IF(MONTH('Sobeys Promo Calendar'[Promo Week Start Date]) < 5, YEAR('Sobeys Promo Calendar'[Promo Week Start Date]) -1 , YEAR('Sobeys Promo Calendar'[Promo Week Start Date]))

And the Fiscal Week using this

Fiscal Week = INT(('Sobeys Promo Calendar'[Promo Week Start Date] - DATE('Sobeys Promo Calendar'[Year],5,1))/7) + 1

NOTE When creating calculated columns it is perfectly fine to use this syntax (without naming the table explicitly) because the columns being referred to, [Promo Week Start Date] and [Year] are in the table where you are creating the column

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

Additional Information

Just as a bit of hopeful useful info for you, when working with dates it's common practice to use a Date Table which is used specifically for creating/storing date related information, like fiscal periods, and is used in time intelligence calculations.

I see that you are using 'Sobeys Promo Calendar'[Promo Week Start Date].[Date] and the .[Date] bit would indicate that you don't have a proper Date Table.

Create the Date Table

DateTable = CALENDAR(MIN('Time_Table'[Week Start Date]), MAX('Time_Table'[Week End Date]))

Create the fiscal year and week

Year = IF(MONTH([Date]) < 5, YEAR([Date]) -1 , YEAR([Date]))

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

Mark the date table as an actual date table and create a relationship between it and your main table, in this example I've called that main table Promo Calendar

Promo Calendar only has a single column of dates

But because it has a relationship to the Date Table you can create visuals with the Date column from Promo Calendar and the fiscal year and week from the Date Table

This gives you the same information in the visual as creating the columns.

Anyway, hope that isn't all information overload.

You can read more on Date Tables here

Design guidance for date tables in Power BI Desktop - Power BI | Microsoft Learn

Creating a simple date table in DAX - SQLBI

Regards

Phil

Re: Week Number Not Returning Week 1 In New Year

Ahmed-Elfeel — Tue, 02 Dec 2025 06:48:33 GMT

Hi L1102,

Oh ok let's give it some corrections touch..here is the corrected DAX formula that should work for your fiscal calendar (May 1 start, Thursday week start):

Fiscal Week Corrected = VAR CurrentDate = 'Sobeys Promo Calendar'[Promo Week Start Date] VAR CurrentYear = YEAR(CurrentDate) VAR May1Current = DATE(CurrentYear, 5, 1) VAR May1Previous = DATE(CurrentYear - 1, 5, 1) // Find first Thursday of May for current year VAR FirstThursdayCurrent = May1Current + SWITCH( WEEKDAY(May1Current, 2), 1, 3, // Monday -> Thursday is +3 days 2, 2, // Tuesday -> Thursday is +2 days 3, 1, // Wednesday -> Thursday is +1 day 4, 0, // Thursday -> no change 5, 6, // Friday -> next Thursday is +6 days 6, 5, // Saturday -> next Thursday is +5 days 7, 4 // Sunday -> next Thursday is +4 days ) // Find first Thursday of May for previous year VAR FirstThursdayPrevious = May1Previous + SWITCH( WEEKDAY(May1Previous, 2), 1, 3, 2, 2, 3, 1, 4, 0, 5, 6, 6, 5, 7, 4 ) // Determine which fiscal year the date belongs to VAR FiscalYearStart = IF( CurrentDate >= FirstThursdayCurrent, FirstThursdayCurrent, FirstThursdayPrevious ) // Calculate days difference VAR DaysDiff = CurrentDate - FiscalYearStart // Calculate week number VAR WeekNum = INT(DaysDiff / 7) + 1 RETURN WeekNum

If the above still has issues, try this version that ensures a full 7 day week calculation:

Fiscal Week Fixed = VAR CurrentDate = 'Sobeys Promo Calendar'[Promo Week Start Date] VAR CurrentYear = YEAR(CurrentDate) // Create May 1 dates for current and previous years VAR May1Current = DATE(CurrentYear, 5, 1) VAR May1Previous = DATE(CurrentYear - 1, 5, 1) // Function to find first Thursday VAR GetFirstThursday = VAR BaseDate = May1Current VAR DayNum = WEEKDAY(BaseDate, 2) VAR DaysToAdd = SWITCH( DayNum, 1, 3, // Monday -> Thursday 2, 2, // Tuesday -> Thursday 3, 1, // Wednesday -> Thursday 4, 0, // Thursday 5, 6, // Friday -> next week 6, 5, // Saturday -> next week 7, 4 // Sunday -> next week ) RETURN BaseDate + DaysToAdd VAR GetFirstThursdayPrevious = VAR BaseDate = May1Previous VAR DayNum = WEEKDAY(BaseDate, 2) VAR DaysToAdd = SWITCH( DayNum, 1, 3, 2, 2, 3, 1, 4, 0, 5, 6, 6, 5, 7, 4 ) RETURN BaseDate + DaysToAdd VAR FirstThursdayCurrent = GetFirstThursday VAR FirstThursdayPrevious = GetFirstThursdayPrevious // Determine correct fiscal year start VAR FiscalYearStart = IF( CurrentDate < FirstThursdayCurrent, FirstThursdayPrevious, FirstThursdayCurrent ) // Calculate week number ensuring it starts at 1 VAR WeekNumber = DIVIDE( CurrentDate - FiscalYearStart, 7, 0 // Returns 0 if error ) + 1 // Ensure week numbers are positive and within range RETURN IF( WeekNumber < 1, 52 + WeekNumber, // Handle rollover from previous year IF( WeekNumber > 52, WeekNumber - 52, // Handle overflow WeekNumber ) )

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: Week Number Not Returning Week 1 In New Year

Rufyda — Tue, 02 Dec 2025 11:20:11 GMT

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

Re: Week Number Not Returning Week 1 In New Year

L1102 — Tue, 02 Dec 2025 16:17:03 GMT

First one worked perfectly!!! thank you so much Ahmed-Elfeel