Group Weekly Starting 1/1

[Anonymous]

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

[mwegener · Accepted solution]

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]

Marcus,

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

[VasTg]

@Anonymous 

Sarah,

Was this post not helpful? Looks like you asked the same question and @amitchandak  already answered it.

https://community.powerbi.com/t5/Desktop/Weekly-start-each-year-on-1-1/m-p/874778

[Anonymous]

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]

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.

[Anonymous]

oh this reply was for the previous comment not yours..