Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Newrr
Frequent Visitor

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
Anonymous
Not applicable

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

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

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

 

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

 

 

Newrr
Frequent Visitor

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?

Anonymous
Not applicable

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

Anonymous
Not applicable

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?
 

Just have year and week number. 

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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. 

 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?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.