Forum Discussion

spandy34's avatar
spandy34
Responsive 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
Super User
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

 

spandy34's avatar
spandy34
Responsive Resident
2 years ago

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)