- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
I tried this and it worked.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Just have year and week number.
Sorry I didn't mean to accept your solution because you didn't offer one yet 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
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.
Subject | Author | Posted | |
---|---|---|---|
06-25-2024 05:40 AM | |||
06-07-2024 03:58 PM | |||
06-07-2023 04:29 AM | |||
10-18-2024 11:25 AM | |||
04-09-2024 12:11 AM |
User | Count |
---|---|
13 | |
12 | |
11 | |
7 | |
7 |
User | Count |
---|---|
13 | |
12 | |
12 | |
9 | |
8 |