Custom Bi-Weekly Week of the Year Column

[Anonymous]

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

[V-pazhen-msft]

@Anonymous 

You might need to do some dax on the week number. Check the pbix for detail.

Paul Zheng _ Community Support Team

If this post helps, please Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

@V-pazhen-msft 

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)

https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

[Anonymous]

@V-pazhen-msft this does help but one small adjustment that I don't know how to fix.  

Instead of the week starting on Sunday, how would this be adjusted to start on Wed and end on Tues? Everything else looks great besides that small details.  Thanks!