Forum Discussion

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
2 years ago
Solved

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             ...
Need Help
  • danextian's avatar
    danextian
    2 years ago

    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

     

danextian's avatar
danextian
Icon for Super User rankSuper User
2 years ago

Hi spandy34 ,

Please try:

 

spandy34's avatar
spandy34
Icon for Responsive Resident rankResponsive Resident
2 years ago

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: