Week Number Not Returning Week 1 In New Year

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
    )
)