Forum Discussion

vincenttys's avatar
vincenttys
Helper I
2 years ago
Solved

Week Number

Hi all,

 

Can i please get help with week number in my date table?

I want my code to be able to show week 53 until the end of June 2025 and start again for week 1 from 01/07/2025. My week need to be start on Sunday and end on Saturday. Please see 29 and 30 of June 2025 which i want it to show 53

 

 

Below is the code i used 

 

Financial Week Number =
VAR WeekNumber = WEEKNUM('Date'[Date],1)
VAR FWeekNumber = WEEKNUM(DATE(YEAR('Date'[Date]),07,01),1)
RETURN IF(
        WeekNumber >= FWeekNumber,
        WeekNumber-(FWeekNumber-1),
        52+WeekNumber-(FWeekNumber-1))
 
 
Thank you.
 
Kind regards,
Vince
  • aduguid's avatar
    aduguid
    2 years ago

    I believe I've sorted the issue.

     

    Calendar = 
VAR _today_date =       TODAY()
VAR _fiscal_year =      YEAR(EDATE( _today_date, 6))
VAR _fy_start =         DATE ( _fiscal_year - 2, 07, 01)
VAR _fy_end =           DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
          CALENDAR(_fy_start, _fy_end)
        , "Calendar Week",  WEEKNUM([Date])
        , "Fiscal Week",    IF(MONTH([Date]) < 7,
            WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1),
            WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1
        )
    )

RETURN
_result

     

     

     

5 Replies

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    If you create a variable to use as an offset, then you can just subtract it to find the fiscal week number.

     

    Calendar = 
VAR _today_date =       TODAY()
VAR _fiscal_offset =    6 // ending month number, if < 6 change to negative
VAR _fiscal_year =      YEAR(EDATE( _today_date, _fiscal_offset))
VAR _fy_start =         DATE ( _fiscal_year - 1, 07, 01)
VAR _fy_end =           DATE ( _fiscal_year, 06, 30)
VAR _fy_week_offset =   WEEKNUM( _fy_start ) - 1

VAR _result = 
    ADDCOLUMNS
    (
          CALENDAR(_fy_start, _fy_end)
        , "Calendar Week",  WEEKNUM([Date])
        , "Fiscal Week",  IF(_fy_week_offset >= WEEKNUM([Date]) || ROUNDUP(MONTH(EDATE([Date], _fiscal_offset)) / 6, 0) = 2, WEEKNUM([Date]) + _fy_week_offset + 2, WEEKNUM([Date]) - _fy_week_offset)
    )

RETURN
_result

     

     

    • vincenttys's avatar
      vincenttys
      Helper I
      2 years ago

      Hi aduguid,

       

      Thanks but i don't it work for me. If i am showing 2 Financial year (From 01/07/2023 and 30/06/2025) and with your DAX, it is missing week 1 around 1st week of July 2024.

      • aduguid's avatar
        aduguid
        Memorable Member
        2 years ago

        I believe I've sorted the issue.

         

        Calendar = 
VAR _today_date =       TODAY()
VAR _fiscal_year =      YEAR(EDATE( _today_date, 6))
VAR _fy_start =         DATE ( _fiscal_year - 2, 07, 01)
VAR _fy_end =           DATE ( _fiscal_year, 06, 30)

VAR _result = 
    ADDCOLUMNS
    (
          CALENDAR(_fy_start, _fy_end)
        , "Calendar Week",  WEEKNUM([Date])
        , "Fiscal Week",    IF(MONTH([Date]) < 7,
            WEEKNUM([Date], 1) + (WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) - 1),
            WEEKNUM([Date], 1) - WEEKNUM(DATE(YEAR([Date]), 7, 1), 1) + 1
        )
    )

RETURN
_result