Change WEEKNUM start

[NochWyn]

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.

[ibarrau · Accepted solution]

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]

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

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,

[RobbeVL]

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/