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 ,

Please try:

 

  • spandy34's avatar
    spandy34
    Responsive Resident
    2 years ago

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

  • spandy34's avatar
    spandy34
    Responsive 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:

     

     

  • spandy34's avatar
    spandy34
    Responsive Resident
    2 years ago

    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'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)