Forum Discussion
Custom Bi-Weekly Week of the Year Column
I'm looking to calculate a column in my date table that will incrementally associate a number with the date every two weeks instead of the traditional week but start on a Wed rather than Mon.
I tried to display below but essentially, every two weeks starting from Day A to end date of Day B which we have running mid week so doing "start of week" formula didn't seem to work well.
The table would be something like the below:
I'm looking to populate the WeekofYear column with an incremental number every two weeks.
| Date | WeekofYear |
| 1/1/2020 | 1 |
| 1/2/2020 | 1 |
1/3/2020 | 1 |
| 1/4/2020 | 1 |
| ......(continues to next date) | 1 |
| 1/14/2020 (next week that is 2 weeks out) | 2 |
| 1/15/2020 | 2 |
| 1/16/2020 | 2 |
| ...(continues to the next ) | 2 |
Anonymous
I was able to find the below blog to get the day to start on Wed but your solution for week.no result did not work for this solution. I reverted to the same values after changing the formula to match the new columns.
week.no result = RANKX('Date','Date'[WeekofYear2 (wed-Tues)],,ASC,Dense)
3 Replies
Anonymous
I was able to find the below blog to get the day to start on Wed but your solution for week.no result did not work for this solution. I reverted to the same values after changing the formula to match the new columns.
week.no result = RANKX('Date','Date'[WeekofYear2 (wed-Tues)],,ASC,Dense)