Weekly start each year on 1/1

[Anonymous]

Hi,

I was wondering if there was a way to find the start of each week of dates for multiple years starting from Jan 1 of each year. 

So far, I have a calculated column that says: 

Init Start of Week = var CurrentDate=LASTDATE(Query1[INIT DATE]) var DayNumberOfWeek=WEEKDAY(LASTDATE(Query1[INIT DATE] ),3) return DATEADD(CurrentDate, -1*DayNumberOfWeek, Day)

which gives a date for each week from 1/1/2018-12/9/2019 

In the first screenshot, it shows that this output has 1/1/2018 but when it turns to 2019, the first date showing is 1/7/2019

In the second screenshot, I am hoping to put the same dates "1/1" and be able to see the outputs for 2018 and 2019, but this shows 1/7 since the first date in 2019 is 1/7/2019

Please let me know if this question is confusing

Thank you!
Sarah 

[amitchandak]

I did not get the question completely. But the formula for the start of week and of the week can be

Week Start date = DATEADD('Date'[Date],-1*WEEKDAY('Date'[Date])+1,DAY)
	Week End date = DATEADD('Date'[Date],7-1*WEEKDAY('Date'[Date]),DAY)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

Is there a way to use this formula so that Week Start Date would calculate for dates starting on Jan 1 for multiple years?

Thank you!
Sarah

[amitchandak · Accepted solution]

Do you want to create your week based weekday of the first Jan?

if all weeks should start as on day 1st Jan

Week Start year based = DATEADD('Compare Date'[Compare Date],-1*WEEKDAY('Compare Date'[Compare Date])+weekday(STARTOFYEAR('Compare Date'[Compare Date])),DAY)

if the first week should start on 1st Jan

Week Start Year wise = if(STARTOFYEAR('Compare Date'[Compare Date])>'Compare Date'[Week Start date],STARTOFYEAR('Compare Date'[Compare Date]),'Compare Date'[Week Start date])

All above formula's column not measures

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[Anonymous]

hi @amitchandak ,

is there any way to deal with leap years?

Leap years are throwing off the number of days per week for leap years....

Thank you!
Sarah

[Anonymous]

Amazing thank you so much!!