Reply
Newrr
Frequent Visitor
Partially syndicated - Outbound

Week number count from a month other than January.

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 

1 ACCEPTED SOLUTION
avatar user
Anonymous
Not applicable

Syndicated - Outbound
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?
 

View solution in original post

10 REPLIES 10
Nikhil_A
New Member

Syndicated - Outbound

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
mh2587
Super User
Super User

Syndicated - Outbound
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 )
    )

Did I answer your question? If so, please mark my post as a solution!


Proud to be a Super User!




LinkedIn Icon
Muhammad Hasnain



Newrr
Frequent Visitor

Syndicated - Outbound

 

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

 

 

Newrr
Frequent Visitor

Syndicated - Outbound

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?

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

avatar user
Anonymous
Not applicable

Syndicated - Outbound
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?
 

Syndicated - Outbound

Just have year and week number. 

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

avatar user
Anonymous
Not applicable

Syndicated - Outbound

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

 

avatar user
Anonymous
Not applicable

Syndicated - Outbound
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. 

Syndicated - Outbound

 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?

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)