Forum Discussion
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 first week, and January 8 would always be the first day of the second week. Any ideas will be appreciated.
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.
4 Replies
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, 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 ) + 1Great! 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,
Hi,
If you create a custom Date Dimension, you can configure it as you wish.
Hope this helps?
https://aleson-itc.com/creando-dimdate-en-power-bi-serie-dimdate-1-3/
