topic Re: Week number count from a month other than January. in DAX Commands and Tips

Week number count from a month other than January.

Newrr — Wed, 25 Jan 2023 13:51:38 GMT

I have a year column and a week number column. I would like to have a week number column in which count starts from April instead of January. So 1st of April will be value 1 and end of march will be 52 or 53.

Can you help please. Thanks

Regards

Re: Week number count from a month other than January.

Anonymous — Wed, 25 Jan 2023 14:10:28 GMT

I remember doing this in a project a while ago, do you have a date column with full date or just a year and week number column?

Re: Week number count from a month other than January.

Anonymous — Wed, 25 Jan 2023 14:18:07 GMT

week number = IF(MONTH('Calendar'[Date]) < 4,

WEEKNUM('Calendar'[Date]) + (52 - WEEKNUM(DATE(YEAR('Calendar'[Date]), 4, 1))),

WEEKNUM('Calendar'[Date]) - WEEKNUM(DATE(YEAR('Calendar'[Date]), 4, 1)) + 1)

This should work if you have a date column. Try it out, if you don't have a date column then it might be difficult, as April 1st isn't a set number of weeks away from Jan 1st so causes issues with the logic.

Re: Week number count from a month other than January.

mh2587 — Wed, 25 Jan 2023 14:22:27 GMT

Fiscal Week Num =
VAR _FiscalWeekStart = 27  --- Put Your Desire Week Number from Where you want to Start from the Calender Week Number 
RETURN
    IF (
        DatesTable[Calendar Week Number] >= _FiscalWeekStart,
        ( DatesTable[Calendar Week Number] ) - ( _FiscalWeekStart - 1 ),
        52 + ( DatesTable[Calendar Week Number] ) - ( _FiscalWeekStart - 1 )
    )

Re: Week number count from a month other than January.

Anonymous — Wed, 25 Jan 2023 14:36:11 GMT

I think there is a problem where if you use this, because you are going off the week number. The week number where April 1st starts is different when a leap year occurs. I think using

week number = IF(MONTH('Calendar'[Date]) < 4, WEEKNUM('Calendar'[Date]) + (52 - WEEKNUM(DATE(YEAR('Calendar'[Date]), 4, 1))), WEEKNUM('Calendar'[Date]) - WEEKNUM(DATE(YEAR('Calendar'[Date]), 4, 1)) + 1)

will get around this problem

Re: Week number count from a month other than January.

Newrr — Wed, 25 Jan 2023 15:37:39 GMT

Just have year and week number.

Sorry I didn't mean to accept your solution because you didn't offer one yet 🙂

Re: Week number count from a month other than January.

Newrr — Wed, 25 Jan 2023 15:41:12 GMT

Thanks for your response. What if it is not necessarily for fiscal year. I mean how do I do if the count is to start for any other month, November for example?

Re: Week number count from a month other than January.

Newrr — Wed, 25 Jan 2023 15:44:03 GMT

mh2587 @Unfortunately I don't have a datetable, just week number and Year

Re: Week number count from a month other than January.

Anonymous — Wed, 25 Jan 2023 15:44:34 GMT

No Problem, my friend! I think this will not be possible without a date column. There are some ways of loosely doing this but over a period of 2 or more years most of the "quick fix" methods are going to be wrong quite quickly.

For example, 1st of April usually starts 12 (and 6 days) weeks after Jan first, so you could just add 12 to the week number then wrap it around when it gets to 52. But on a leap year, it's 13 weeks so the data will start to be incorrect by a day as the years go on. There are also a multitude of other issues but this is just an example.

Difficult to do this without a full date column. I will have a think as the day goes on but hopefully someone else comes up with an idea. Good luck, i'll pm you or message you here if i think of something

Re: Week number count from a month other than January.

Newrr — Wed, 25 Jan 2023 18:50:44 GMT

thanks Anonymous for your response. Unfortunately I don't have a calendar table, just week number year but month could be added. Would It help?

Re: Week number count from a month other than January.

Nikhil_A — Fri, 14 Jun 2024 16:28:02 GMT

Hi,

I tried this and it worked.

FY WK Num = IF(MONTH('Calendar Table'[Date])<4,

WEEKNUM('Calendar Table'[Date],1)+(WEEKNUM(ENDOFYEAR('Calendar Table'[Date]),1)-WEEKNUM(DATE(YEAR('Calendar Table'[Date]),4,1))),

WEEKNUM('Calendar Table'[Date])-WEEKNUM(DATE(YEAR('Calendar Table'[Date]),4,1))+1)

Everything marked in Orange is to be changed according to your data.

(FY WK Num = Column name as desired)

'Calendar Table'[Date] = Table name and date column

4 = Desired start month of the year

,1 (Only hilighted) = Week start day (in this case its sunday)

Regards

Nikhil