cancel
Showing results for 
Search instead for 
Did you mean: 
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
ADPowerBI1
Resolver II
Resolver II

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

9 REPLIES 9
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 )
    )


Muhammad Hasnain

#LetsConnect https://www.linkedin.com/in/hasnain2587/
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?

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

ADPowerBI1
Resolver II
Resolver II

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 🙂

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

 

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 @ADPowerBI1  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
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.