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

     

     

     

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

 

 

 

vincenttys's avatar
vincenttys
Helper I
2 years ago

Hi aduguid,

 

Thanks it work. 

 

I managed to tweak my original code to get what i need and again thanks for your help.

  • aduguid's avatar
    aduguid
    Memorable Member
    2 years ago

    no worries, anytime