Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Group Weekly Starting 1/1

Hi,

 

Is there any way to start a grouping of weeks starting on Jan 1? I have this formula:

Date Start of Week = DATEADD(Dates[Date],-1*WEEKDAY(Dates[Date])+weekday(STARTOFYEAR(Dates[Date])),DAY)
but this starts the count of the week on Sunday when I want it to start on 1/1
So for example, the highlighted "1/8/2013" would instead say "1/1/2013" since from 1/1, those two days will be included in the week
 
Thank you!
Sarah
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous ,

     

    try this.

     

    Date Start of Week =
DATE ( YEAR ( Dates[Date] ), 1, 1 )
    + QUOTIENT ( DATEDIFF ( STARTOFYEAR ( 'Dates'[Date] ), Dates[Date], DAY ), 7 ) * 7

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support. 

6 Replies

  • mwegener's avatar
    mwegener
    Icon for Most Valuable Professional rankMost Valuable Professional
    6 years ago

    Hi Anonymous ,

     

    try this.

     

    Date Start of Week =
DATE ( YEAR ( Dates[Date] ), 1, 1 )
    + QUOTIENT ( DATEDIFF ( STARTOFYEAR ( 'Dates'[Date] ), Dates[Date], DAY ), 7 ) * 7

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Marcus,

      Amazing!! Thank you so so much for your help again!
      Sarah

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      This was helpful, but it did not group the full week for each year starting on 1/1.

      For example, in 2013, there were only 5 days in the week "1/1" since the week in the formula given started on sunday, and sunday was 12/30/2012.

      I wanted to display 7 days in the week 1/1 for all years

       

      Sarah

      • mwegener's avatar
        mwegener
        Icon for Most Valuable Professional rankMost Valuable Professional
        6 years ago

        Hi Anonymous 

         

        i don't understand what is wrong?

        Regards,

        Marcus

        Dortmund - Germany
        If I answered your question, please mark my post as solution, this will also help others.
        Please give Kudos for support.