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
Solved! Go to Solution.
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 ) )
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
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
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?
Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.
Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!
Join the biggest FREE Business Applications Event in LATAM this February.
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.