Forum Discussion

NochWyn's avatar
NochWyn
Frequent Visitor
7 years ago
Solved

Change WEEKNUM start

I need to figure out how to alter the week calculation to not be dependent on starting on Sunday or Monday, but on January 1 every year. For example, January 7 should always be the last day of the fi...
Need Help
  • ibarrau's avatar
    ibarrau
    7 years ago

    Hi. The easiest way I figure out right now is adding a new custom column with Power Query in the same table that has the Date Column. The code should be like this:

    Number.RoundUp(Date.DayOfYear([Fecha]) / 7)

    Hope this help.

    Regards,

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
7 years ago

Hi. The easiest way I figure out right now is adding a new custom column with Power Query in the same table that has the Date Column. The code should be like this:

Number.RoundUp(Date.DayOfYear([Fecha]) / 7)

Hope this help.

Regards,

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • NochWyn's avatar
    NochWyn
    Frequent Visitor
    7 years ago

    ibarrau, thank you for the suggestion! Your solution triggered the idea to just use the round function directly in Power BI > Modeling > New Column.

     

    Week = ROUNDUP( Dates[DayNoOfYear] / 7, 0 )
     
    And I get day number of year [DayNoOfYear] from:
     
    DayNoOfYear = DATEDIFF ( DATE ( YEAR ( Dates[Date] ), 1, 1 ), Dates[Date], DAY ) + 1
     
    • ibarrau's avatar
      ibarrau
      Icon for Super User rankSuper User
      7 years ago

      Great! did the post solve your problem? if so, please mark it as solution, if not please let me know how can I continue helping you :)

       

      Regards,