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))

date table with DAX

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)

DAX ISO week numbering

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])

ISO Year in DAX, ISO Year in Power BI

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")

ISO Week and Year in DAX

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.

Posted

June 16, 2021

DAX

Comments

4 responses to “How to calculate ISO week number and ISO year in DAX”

Markus
September 24, 2021
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.
Reply
1. Janis Sturis
  September 24, 2021
  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.
  Reply
SD Power BI User
December 16, 2021
How to I create a calendar function and automatically add the week number? I tried but it seems like that DAX is for the table itself?
For example, can the isoweek, isoyear, date of the week start be added to a table like this?
Date =
ADDCOLUMNS (
CALENDAR (DATE(2020,1,1), TODAY()-1),
“DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
“Year”, YEAR ( [Date] ),
“Monthnumber”, FORMAT ( [Date], “MM” ),
“YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
“YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
“MonthNameShort”, FORMAT ( [Date], “mmm” ),
“MonthNameLong”, FORMAT ( [Date], “mmmm” ),
“DayOfWeekNumber”, WEEKDAY ( [Date] ),
“DayOfWeek”, FORMAT ( [Date], “dddd” ),
“DayOfWeekShort”, FORMAT ( [Date], “ddd” ),
“Quarter”, “Q” & FORMAT ( [Date], “Q” ), “Week”,”Week-“&FORMAT([Date],”WW”),”Week Number”,FORMAT([Date],”WW”),
“YearQuarter”, FORMAT ( [Date], “YYYY” ) & “/Q” & FORMAT ( [Date], “Q” )
)
Reply
1. Janis Sturis
  December 21, 2021
  Yes, it can be added.
  Date =
  ADDCOLUMNS (
  CALENDAR (DATE(2020,1,1), TODAY()-1),
  “DateAsInteger”, FORMAT ( [Date], “YYYYMMDD” ),
  “Year”, YEAR ( [Date] ),
  “Monthnumber”, FORMAT ( [Date], “MM” ),
  “YearMonthnumber”, FORMAT ( [Date], “YYYY/MM” ),
  “YearMonthShort”, FORMAT ( [Date], “YYYY/mmm” ),
  “MonthNameShort”, FORMAT ( [Date], “mmm” ),
  “MonthNameLong”, FORMAT ( [Date], “mmmm” ),
  “DayOfWeekNumber”, WEEKDAY ( [Date] ),
  “DayOfWeek”, FORMAT ( [Date], “dddd” ),
  “DayOfWeekShort”, FORMAT ( [Date], “ddd” ),
  “Quarter”, “Q” & FORMAT ( [Date], “Q” ),
  “Week”,”Week-“&FORMAT([Date],”WW”),
  “Week Number”,FORMAT([Date],”WW”),
  “YearQuarter”, FORMAT ( [Date], “YYYY” ) & “/Q” & FORMAT ( [Date], “Q” ),
  “ISO Week Number”, WEEKNUM([Date], 21),
  “ISO Year Number”, YEAR([Date] + 26 – WEEKNUM([Date], 21)),
  “Week Start Date”, [Date] – WEEKDAY ( [Date], 3 )
  )
  Reply