topic Re: Week Number in DAX Commands and Tips

Week Number

vincenttys — Mon, 13 May 2024 02:11:12 GMT

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

Re: Week Number

aduguid — Mon, 13 May 2024 07:29:05 GMT

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

Re: Week Number

vincenttys — Mon, 13 May 2024 22:26:47 GMT

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.

Re: Week Number

aduguid — Mon, 13 May 2024 22:45:46 GMT

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

Re: Week Number

vincenttys — Mon, 13 May 2024 23:00:28 GMT

Hi aduguid,

Thanks it work.

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

Re: Week Number

aduguid — Mon, 13 May 2024 23:03:24 GMT

no worries, anytime