How to fix Date.WeekOfYear behavior?

Power Query
1

When using the WeekOfYear PowerQuery function I found the following two issues:

  1. If a week, starting with Monady and ending with Sunday, goes into the next year, then the same week gets a different number in the old year and in the new year. But same week must have same number, because it’s the same week.
  2. If January first is a Friday, Saturday, or Sunday then all week numbers are 1 too high for the whole year!

Example:
image

In this example 53 would be the correct week number for January 1st 2021, January 2nd 2021 and January 3rd 2021. And 1 would be the correct week number for January 4th 2021 and so on.These are at least the rules in Germany.

Are there parameters to make Date.WeekOfYear work like a calendar, or is there a different function, or do I need to fix this by programming some logic around Date.WeekOfYear?

Thank you!
Martin

2

This is the code I came up with to fix this. It’s a bit cumbersome because the logic is as follows:

  1. If January 1st of the same year is Friday, Saturday or Sunday, then deduct 1 from Date.WeekOfYear

  2. If #2 results in 0 then continue with last week number of previous year, whereas this last week number of previous year can be the result Date.WeekOfYear or the corrected Date.WeekOfYear which is deducted by 1, depending on what day of the week is January 1st of the previous year.

  3. The last days of december can already belong to the 1st week of the next year, and if not, they can be the corrected or not corrected last week of the current year.

    if (
    Date.DayOfWeek(
    #date([Year], 1, 1),
    Day.Monday) > 3
    )
    then
    if Date.WeekOfYear([Date]) - 1 = 0
    then
    if (
    Date.DayOfWeek(
    #date([Year] - 1, 1, 1),
    Day.Monday) > 3
    )
    then
    Date.WeekOfYear(
    #date([Year] - 1, 12, 31)
    ) - 1
    else Date.WeekOfYear(
    #date([Year] - 1, 12, 31)
    )
    else
    if [Monat] = 12 and [Tag] > 27 and Date.DayOfWeek(
    #date([Year] + 1, 1, 1),
    Day.Monday) < 4
    then
    if Date.DayOfWeek([Date]) < 4
    then 1
    else Date.WeekOfYear([Date]) - 1
    else Date.WeekOfYear([Date]) - 1
    else
    if [Monat] = 12 and [Tag] > 27 and Date.DayOfWeek(
    #date([Year] + 1, 1, 1),
    Day.Monday) < 4
    then
    if Date.DayOfWeek([Date]) < 4
    then 1
    else Date.WeekOfYear([Date])
    else Date.WeekOfYear([Date])

3

Power Query: Date.WeekOfYear( dateTime as any, optional firstDayOfWeek)
i.e. : Date.WeekOfYear(#date(2011, 03, 27), Day.Monday) Returns 13.
Date.WeekOfYear(#date(2011, 03, 27)) Returns 14.

1 Like
4

Thank you, marcster_uk! This is another Power Query specific behavior, that the default first day in a week is Sunday, whereas in the whole continental-europe business world the week starts with Monday and ends with - guess what - the weekend :smiley:
But no matter what your first day of the week is, Sunday or Monady or default, you have those mid-week jumps in the numbering when a new year begins. The most tricky part of this behavior in my opinion is that in some years these DAX week numbers match with the calendar week in an ordinary calendar and in some they don’t. So maybe my report looks right when I hand it over to a customer and the next year the customer goes crazy because the week numbers are wrong.
My code fixes this issue. I’m only wondering whether there is a simpler, build-in solution.

5

@Martin,

You can give this a try:

if 
    Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=0
then
    Number.RoundDown((Date.DayOfYear(#date(Date.Year([Date])-1,12,31))-(Date.DayOfWeek(#date(Date.Year([Date])-1,12,31), Day.Monday)+1)+10)/7)
else if
    (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), Day.Monday)+1<4))
then
    1
else
    Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], Day.Monday)+1)+10)/7)

.
Details can be found in this article:

6

Thank you @Melissa , I like the short, clean style of your solution! Sorry for the late response, but I was busy with a Shapemap problem I’ll post about later.
BR
Martin