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.

     

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,