Group by Weeks

[spandy34]

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

@v-heq-msft @Greg_Deckler @danextian @tamerj1 @goncalogeraldes 

[danextian · Accepted solution]

Hi @spandy34 ,

Please try:

[danextian · Accepted solution]

Hi @spandy34 ,

Please try:

[spandy34]

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?

[danextian · Accepted solution]

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

[spandy34]

You are correct - I looked at the source of the Week Nos column and it was resetting after each year back to 1 so I have used the following DAX for the Week Nos and it is continuous.  I then used the calculation you provided for the grouping and it has worked .  The DAX for the two columns are below.  Thank you for your help

WeekNos = QUOTIENT( datediff(minx('Procurement_Delivered_Visit_Date',[Date]),[Date],DAY),7)+1

12 Weeks Period = ROUNDUP(DIVIDE('Procurement_Delivered_Visit_Date'[WeekNos],12),0)

[spandy34]

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:

---

[spandy34]

That is brilliant. It worked.  Thank you very much @danextian 

[spandy34]

Could this be done by creating the new column if I grouped by every 6 weeks instead of every 12 weeks ?

[lbendlin]

Quarters and weeks are incompatible. Use an external static reference table that clearly identifies the quarter and the week number for every single day.

[spandy34]

Could this be done by creating the new column if I grouped by every 6 weeks instead of every 12 weeks ?

[lbendlin]

Not sure what you mean. Please elaborate.

[spandy34]

I just want a new column that says :

if the Week No column is between 1 and 11 then 1

if the Week No is between 12 and 23 then 2

if the Week No is between 24 and 35 then 3 and so on