Week Number Not Returning Week 1 In New Year

[L1102]

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.

[Rufyda]

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 

[Ahmed-Elfeel]

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.

[L1102]

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.

[Ahmed-Elfeel · Accepted solution]

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.

[L1102]

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

[PhilipTreacy]

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

[L1102]

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.

[PhilipTreacy]

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

[Zanqueta]

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

[L1102]

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

[rohit1991]

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 )

[L1102]

Hi @rohit1991 

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