Forum Discussion

L1102's avatar
L1102
Helper I
8 months ago
Solved

Week Number Not Returning Week 1 In New Year

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 dat...
  • Ahmed-Elfeel's avatar
    Ahmed-Elfeel
    8 months ago

    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.
Ahmed-Elfeel's avatar
Ahmed-Elfeel
Super User
8 months ago

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's avatar
L1102
Helper I
8 months ago

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's avatar
    Ahmed-Elfeel
    Super User
    8 months ago

    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's avatar
      L1102
      Helper I
      8 months ago

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