Understanding Power BI Weekday Configurations Starting from Monday

Introduction:

One of the frequent tasks when working with dates in Power BI is to determine the start date of a week using a given week number. Though it seems straightforward, varying standards in week numbering can make this a bit intricate. This guide provides a detailed walkthrough on determining the starting Monday of a week using the week number, highlighting nuances and the essential formulas.

Why Calculate the Start Date from a Week Number?

Let’s consider a practical example: if provided with ‘week 3 /2021’, the goal is to return Monday, 18th January 2021. The fundamental approach is to add the necessary weeks to the 1st of January of the target year. Subsequently, identifying the Monday of that week becomes crucial, factoring in different week numbering methodologies.

Distinguishing Week Numbers and ISO Week Numbers

When it comes to week numbering, there are two primary methods: ISO and non-ISO. In the ISO scheme, for instance, January 1st, 2021 falls in week 53. However, in a standard week numbering scheme, January 1st always corresponds to week 1.

Extracting the Start Date from a Standard Week Number

For the standard week number, utilize the following formula, substituting targetYear and targetWeekNo with the desired values:

With({targetYear:2021 , targetWeekNo:3},
      With({targetDate: DateAdd(Date(targetYear,1,1), 7* targetWeekNo)},
           DateAdd(targetDate,
                   -(Weekday(targetDate)),
                   Days
           )
      )
)
    

Reference:

• With function

This approach takes the 1st January of the target year and appends the number of weeks. The output aligns with our expectations: Sunday, 10th January 2021.

Deriving the Start Date from an ISO Week Number

Computing the start date based on an ISO week number is slightly complex due to variations like January 1st not always being week 1. Here’s the appropriate formula:

With({targetYear:2021 , isoWeekNo:35},
With({isoDate:
      If(ISOWeekNum(Date(targetYear,1,1)) < 1,
         DateAdd(Date(targetYear,1,1), 7* (isoWeekNo-1)),
         DateAdd(Date(targetYear,1,1), 7* isoWeekNo)
      )
      },
      DateAdd(isoDate,
        -(Weekday(isoDate,StartOfWeek.MondayZero)),
        Days
   )
 )
)
    

Note that for ISO week numbers, Monday is the week’s start. This formula correctly returns Monday, 18th January 2021, for our case.

Conclusion:

When dealing with Power BI and the task of extracting the start date of a week from a week number, understanding the subtle differences between ISO and standard week numbers is pivotal. Armed with the right formula, one can easily compute the required date, ensuring accurate results and improved data analytics. Should you face any challenges or require additional assistance, do not hesitate to contact us for expert help.