# How to calculate ISO week number and ISO year in DAX

ISO week number and ISO year calculation in DAX might be the best solution if you’re using the Power BI calculated table. They will give you the dimension of weeks with the same number of days and work for multiple years.

In DAX, it is similar to Excel.

To demonstrate all these calculations, I will generate a date table with 10 days.

`Date = CALENDAR(DATE(2021, 1, 1), DATE(2021, 1, 10))`

## ISO week number in DAX

You can calculate the ISO week number by using the function WEEKNUM. In the WEEKNUM, you should use return type argument 21 to calculate week number by ISO 8601 calendar standard. It might be confusing when the autocomplete doesn’t show that kind of return type, but you can see in the function WEEKNUM description that that is an option.

`ISOWeekNumber = WEEKNUM('Date'[Date], 21)`

## ISO year in DAX

ISO year is necessary if you have multiple years and want to group data using ISO week number. Some of the dates at the end or beginning of the year might belong to another year.

If you already calculated the ISO week number, then it is easy to do mathematically like this.

`ISOYear = YEAR('Date'[Date] + 26 - 'Date'[ISOWeekNumber])`

## Other calculations

When you have ISO week number and ISO year in DAX, you can combine them in multiple ways.
For example, as a number or as a text.

```ISOWYNumber = 'Date'[ISOWeekNumber] * 10000 + 'Date'[ISOYear]
ISOYWNumber = 'Date'[ISOYear] * 100 + 'Date'[ISOWeekNumber]
ISOYWtext = 'Date'[ISOYear] & " " & FORMAT('Date'[ISOWeekNumber], "00")```

As you can see, I added leading zeros in DAX for one of the results.

Here is how to do these calculations one step earlier in Power Query – ISO year and ISO week number.

### 2 comments on “How to calculate ISO week number and ISO year in DAX”

1. Markus

Hello Jānis,
that trick really save my day. That was what I looking for. We always had weeknumber to transform to ISO but nothing for the Year to create a own string like YYYY / MM.
But there is one thing I find interesting:
ISOYear = YEAR(‘Date'[Date] + 26 – ‘Date'[ISOWeekNumber])
Where does the number 26 coming from? Is it the half of 53 weeks -1 or …?

Even if I would take out the 26 and just use this:
ISOYear = YEAR(‘Date'[Date] – ‘Date'[ISOWeekNumber])
I would get the right calendar week for the years in the past and future that I checked. Also it wouldn’t make difference in around the 26th week of a year. I hope you can elaborate on this.
Have a nice weekend.
Best regards.

• Janis Sturis

Hello, Marcus!

Most of the time, it will work without this weird constant, but not on 01.01.2024 or 01.01.2019.
I do not have a beautiful explanation for 26. It is a middle point of the typical number of weeks and balancing out some shortcomings when years change.
The good thing is that you can test that for long periods and it works.

Nice weekend for you too.