#PowerQuery – Calculate the ISO date from year and a week number

Just wanted to share a M function that I just created to calculate the date from a Year and a ISO week number.

The example data is the following

Now the function I created can be called with the Year and Week number as parameters to get the following result

The function has the following code and will return the date of the Monday of the week number.

(TheYear as number, TheWeek as number) as date =>
let
//test
//TheYear = 2018,
//TheWeek = 1,
//
offsetToISO = Date.AddDays(#date(TheYear,1,1),-4),
dayOfWeek = Date.DayOfWeek(offsetToISO),
offset = -dayOfWeek + (TheWeek * 7),
isoWeekDate = Date.AddDays(offsetToISO, offset)
in
isoWeekDate

Hope this can help you too.

Here is a link to an example pbix file – link

One thought on “#PowerQuery – Calculate the ISO date from year and a week number

  1. Pingback: Calculating ISO Date with M – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s