Forum Discussion
Group by Weeks
Hello
I have a Date Table and I would like to create a column named Qtr/Weeks which groups the Week Nos by 12 , so each Qtr Week contains 12 weeks ie
Qtr/Weeks WeekNo
1 0-11
2 12-23
3 24-35
4 36-47
5 48-52
Can someone please tell me how to create this column in the table below.
Many thanks
Anonymous Greg_Deckler danextian tamerj1 goncalogeraldes
Hi spandy34 ,
Does the reference week number reset? If the, the formula i proposed would reset as well.
I dont know how you number your weeks but the sample formula below calculates for the week number from a reference up to the current row date.Week number from start = QUOTIENT ( DATEDIFF ( DATE ( 2022, 1, 1 ), 'Calendar'[Date], DAY ), 7 ) + 1
11 Replies
Quarters and weeks are incompatible. Use an external static reference table that clearly identifies the quarter and the week number for every single day.
Could this be done by creating the new column if I grouped by every 6 weeks instead of every 12 weeks ?
Hi danextian
I added the column you suggested and it worked great apart from the 12 Weeks Period is going back to 1 at the beginning of the new year so for Date 01 January 2024 the 12 Weeks Period has gone back to 1 but there are not 12 weeks in Period 5. How do I get the column so it just rolls on without returning back to 1 for a new year?
danextian wrote:Hi spandy34 ,
Please try:
hI danextian
This works if I use
Qtr = ROUNDUP(DIVIDE('Procurement_Delivered_Visit_Date'[WeekNo],12),0) but it still resets at the end of the year as mentioned and I need to be continuous and not reset and going back to 1 at the beginning of the new year so for Date 01 January 2024 the 12 Weeks Period has gone back to 1 but there are not 12 weeks in Period 5. How do I get the column so it just rolls on without returning back to 1 for a new year?Hi spandy34 ,
Does the reference week number reset? If the, the formula i proposed would reset as well.
I dont know how you number your weeks but the sample formula below calculates for the week number from a reference up to the current row date.Week number from start = QUOTIENT ( DATEDIFF ( DATE ( 2022, 1, 1 ), 'Calendar'[Date], DAY ), 7 ) + 1
