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

5 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

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