Forum Discussion

sharath_15's avatar
sharath_15
Regular Visitor
7 months ago
Solved

Calendar week logic not working

Hi All


Relative_weeek =
VAR CurrentISOYear = YEAR(TODAY() - WEEKDAY(TODAY(), 2) + 4)
VAR CurrentISOWeek = WEEKNUM(TODAY() - WEEKDAY(TODAY(), 2) + 4, 2)


VAR TestISOYear = YEAR('sales'[sale_date] - WEEKDAY('sales'[sale_date], 2) + 4)
VAR TestISOWeek = WEEKNUM('sales'[sale_date] - WEEKDAY('sales'[sale_date], 2) + 4, 2)

RETURN (CurrentISOYear * 52 + CurrentISOWeek) - (TestISOYear * 52 + TestISOWeek)

For the above DAX when I select relative week as 4 (considering today as Jan-21-2026), I am getting two week values. i.e., 2025-52 and 2026-52. ideally 2026-52 should not appear.

Refer to below image for detailed info.


FYI: Below are columns used to derive year and week values in the visual.
,RIGHT('0' + CAST(DATEPART(ISO_WEEK, SALES_DATE) AS VARCHAR(2)), 2) AS CW -- Week number in a year
,FORMAT(DATEADD(DAY, (7 - (DATEPART(WEEKDAY, SALES_DATE) - 1)), SALES_DATE), 'yyyy') AS [year]

Struck with this from long time.
Any help would be very greatful and highly appreciated.

Need Help
  • cengizhanarslan's avatar
    cengizhanarslan
    7 months ago

    Some years have 53 ISO weeks, so Year*52 + Week will produce edge-case mismatches. Use week start dates + DATEDIFF instead:

     

    Relative_Week =
VAR CurrentWeekStart =
    TODAY () - WEEKDAY ( TODAY (), 2 ) + 1   -- Monday of current week (as of refresh time)
VAR ThisWeekStart =
    'sales'[sale_date] - WEEKDAY ( 'sales'[sale_date], 2 ) + 1
RETURN
DATEDIFF ( ThisWeekStart, CurrentWeekStart, WEEK )

     

3 Replies

  • cengizhanarslan's avatar
    cengizhanarslan
    Super User
    7 months ago

    Some years have 53 ISO weeks, so Year*52 + Week will produce edge-case mismatches. Use week start dates + DATEDIFF instead:

     

    Relative_Week =
VAR CurrentWeekStart =
    TODAY () - WEEKDAY ( TODAY (), 2 ) + 1   -- Monday of current week (as of refresh time)
VAR ThisWeekStart =
    'sales'[sale_date] - WEEKDAY ( 'sales'[sale_date], 2 ) + 1
RETURN
DATEDIFF ( ThisWeekStart, CurrentWeekStart, WEEK )

     

  • v-tejrama's avatar
    v-tejrama
    Community Support
    7 months ago

    Hi sharath_15 ,

     

    Thank you cengizhanarslan  for the response provided!

    Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Best Regards,
    Tejaswi.
    Community Support

    • v-tejrama's avatar
      v-tejrama
      Community Support
      7 months ago

      Hi sharath_15 ,

       

      I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.

      Thank you.