Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Weekly start each year on 1/1

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 Star...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    Amazing thank you so much!!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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