Forum Discussion
Helper ICustom Weeknum
Hello,
I'm trying to create a column with custom week numbers (Operating periods) that end in the dates below:
1/1/2022 - 1/12/2022 - Week 1
1/13/2022 - 1/19/2022 - Week 2
1/20/2022 - 1/26/2022 - Week 3
Until the last week of the year.
Any ideas on how I can accomplish this? I a pprecite your help!
To close the loop on this, this is what ended up working:
Operating Period = WEEKNUM('Calendar'[Date]+4, 21)-1
It gave me this:
To correct the first 5 days of the week that defaulted to Operating Period 0, I added this:
Operating Periods = if('Calendar'[Operating Period] = 0,1,WEEKNUM('Calendar'[Date]+4, 21)-1)
The result was this, which is what I needed:
3 Replies
- Greg_Deckler
Community Champion
NPC What is the pattern? Is the first week 1/1 - 1/12 and then each week is the next 7 days? You could do something like maybe: WEEKNUM([Date],14) ? 14 starts weeks on Thursday.
NPCUnfortunatley with this approach week 1 ends on 1/5. To answer your question though, yes - each week is the next 7 days after the first 12 days of the year.
- NPC
To close the loop on this, this is what ended up working:
Operating Period = WEEKNUM('Calendar'[Date]+4, 21)-1
It gave me this:
To correct the first 5 days of the week that defaulted to Operating Period 0, I added this:
Operating Periods = if('Calendar'[Operating Period] = 0,1,WEEKNUM('Calendar'[Date]+4, 21)-1)
The result was this, which is what I needed:
