# ISO 8601 Week in DAX

I recently built a PowerPiovt model where I had to display weeks according to ISO 8601. As I came across this frequently in the past when I developed SQL Server databases (prior to 2008) I was aware that ISO weeks can also be calculated using some complex logics. When I discussed this with some of my colleagues during a training, one of the attendees told me, that this can be solved in Excel very easily using Excels WEEKNUM()-function. This function takes to arguments:

Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.

Return_type is a number that determines on which day the week begins. The default is 1.

Return_type Week Begins
1 Week begins on Sunday. Weekdays are numbered 1 through 7.
2 Week begins on Monday. Weekdays are numbered 1 through 7.

According to Excels Online-Help the second parameter only supports values 1 and 2. But this is not 100% true. You can also use the value 21 as second parameter, and guess what – it now returns weeknumbers according to ISO 8601. As many DAX-functions are derived from Excel (including WEEKNUM()-function), this also works with DAX’s WEEKNUM()-function!

So by creating a calculated column as

you get the ISO week number for the current [Date].

If you also want to calculate “ISO years” to build clean hierarchies you may want to use this formula in an other calculated column:

I think we can learn a lot from experienced Excel-users here as most tricks also work in PowerPivot/DAX!

## 14 Replies to “ISO 8601 Week in DAX”

1. haha this is cool..

2. Thanks for sharing Gerhard, exactly what I needed!

3. An alternative way of getting the ISO week year

Iso week rules state that the first week of the year always contains 4 January. Therefore the thursday in the same week as a given date always has the correct year. In a formula:

ISO week year = YEAR([Date] + 4 – WEEKDAY([Date];2))

Some more of my favorite ISO formulas

The weekday numbers starting with monday = 1
Weekday number = WEEKDAY([Date];2)

The number of a week following the ISO 8601 standard (https://en.wikipedia.org/wiki/ISO_week_date)
ISO week number = WEEKNUM([Date];21)

The official ISO week extended notation 2018-W30
ISO week extended = FORMAT(YEAR([Date] + 4 – [Weekday number]);”0″) & “-W” & FORMAT([ISO week number];”00″)

The official ISO week compact notation 201830
ISO week compact = FORMAT(YEAR([Date] + 4 – [Weekday number]);”0″) & FORMAT([ISO week number];”00″)

The official ISO date extended notation 2018-W30-4
ISO week date extended = FORMAT(YEAR([Date] + 4 – [Weekday number]);”0″) & “-W” & FORMAT([ISO week number];”00″) & “-” & FORMAT([Weekday number];”0″)

The official ISO date compact notation 2018304
ISO week date compact = FORMAT(YEAR([Date] + 4 – [Weekday number]);”0″) & FORMAT([ISO week number];”00″) & FORMAT([Weekday number];”0″)

• Thanks for sharing your findings and experience!

4. Thanks for the information. I too was trying to build a date table in Power BI and was having some problems. Surprised they didn’t mention this in Microsoft’s DAX function reference site. Especially since they mention the ISO 8601 calendar difference.

5. Great solution. Would love to know how you found that you can use the “21” parameter in the week number function?

6. Thanks – simple solution that works.

7. This is great, thank you! Made all the difference in my calendar 🙂
Do you have any tips, how to make ISO-month matching the ISO-weeks?

• Hi Maria,
For ISO-months you will usually follow a 4-4-5 logic or something similar to match ISO-weeks to months. This can be easily accomplished using an IF/SWITCH function

Kind regards
-gerhard