ISO 8601 Week in DAX

Posted on by — 23 Comments ↓

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

=WEEKNUM([Date], 21)

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:

=IF([ISOWeek]<5 && [CalendarWeek] > 50;
     [Year]+1;
     IF([ISOWeek]>50 && [CalendarWeek]<5; 
          [Year]-1; 
          [Year]))

 

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

If you want to do the same in Power Query already, here is some sample code (original source)

let
    StartDate        = #date (2009,1,1),
    EndDate          = #date (2024,12,31),
    ListOfDates      = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    DurationDays     = Duration.Days (EndDate - StartDate) + 1,
    TableOfDates     = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DateColText      = Table.RenameColumns(TableOfDates,{{"Column1", "Date"}}),
    DateCol          = Table.TransformColumnTypes(DateColText,{{"Date", type date}}),
    WeekdayCol       = Table.AddColumn(DateCol, "Weekday Number", each Date.DayOfWeek([Date], Day.Monday) + 1,Int64.Type),
    IsoWeekYearCol   = Table.AddColumn(WeekdayCol, "Iso Week Year", each Date.Year(Date.AddDays([Date], 4-[Weekday Number])), Int64.Type),
    IsoWeekNrCol     = Table.AddColumn(IsoWeekYearCol, "Iso Week Nummer", each (Duration.Days(Date.AddDays([Date], 4-[Weekday Number]) - #date([Iso Week Year], 1 , 7 - Date.DayOfWeek(#date([Iso Week Year],1,4), Day.Monday)))/7)+1, Int64.Type),
    IsoWeekdayExtCol = Table.AddColumn(IsoWeekNrCol, "Iso Weekday Extended", each Text.From([Iso Week Year]) & "-W" & Text.End("0" &     Text.From([Iso Week Nummer]), 2) & "-"&  Text.From([Weekday Number]))
in
    IsoWeekdayExtCol

23 Replies to “ISO 8601 Week in DAX”

  1. Pingback: Hidden ISO week functionality « Excellereer

  6. 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″)

    Reply ↓

  8. 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.

    Reply ↓

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

    Reply ↓

  11. 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?

    Reply ↓

    • 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

      Reply ↓

  15. I get the message “Column ‘Weekday number’ cannot be found or may not be used in this expression.

    Reply ↓

    • whats the full code that you are using?
      the column [Weekday Number] needs to be part of your table already
      it may be calculated as Weekday number = WEEKDAY([Date];2) as in the comments above

      Reply ↓

  16. I have a code like this one.. are you able to help me to figure it out (concerning with the ISO Week number)

    let fnDateTable = (StartDate as date, EndDate as date, FYStartMonth as number) as table =>
    let
    DayCount = Duration.Days(Duration.From(EndDate – StartDate)),
    Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
    TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
    ChangedType = Table.TransformColumnTypes(TableFromList,{{“Column1”, type date}}),
    RenamedColumns = Table.RenameColumns(ChangedType,{{“Column1”, “Date”}}),
    InsertYear = Table.AddColumn(RenamedColumns, “Year”, each Date.Year([Date]),type text),
    InsertYearNumber = Table.AddColumn(RenamedColumns, “YearNumber”, each Date.Year([Date])),
    InsertQuarter = Table.AddColumn(InsertYear, “QuarterOfYear”, each Date.QuarterOfYear([Date])),
    InsertMonth = Table.AddColumn(InsertQuarter, “MonthOfYear”, each Date.Month([Date]), type text),
    InsertDay = Table.AddColumn(InsertMonth, “DayOfMonth”, each Date.Day([Date])),
    InsertDayInt = Table.AddColumn(InsertDay, “DateInt”, each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth]),
    InsertMonthName = Table.AddColumn(InsertDayInt, “MonthName”, each Date.ToText([Date], “MMMM”), type text),
    InsertCalendarMonth = Table.AddColumn(InsertMonthName, “MonthInCalendar”, each (try(Text.Range([MonthName],0,3)) otherwise [MonthName]) & ” ” & Number.ToText([Year])),
    InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, “QuarterInCalendar”, each “Q” & Number.ToText([QuarterOfYear]) & ” ” & Number.ToText([Year])),
    InsertDayWeek = Table.AddColumn(InsertCalendarQtr, “DayInWeek”, each Date.DayOfWeek([Date])),
    InsertDayName = Table.AddColumn(InsertDayWeek, “DayOfWeekName”, each Date.ToText([Date], “dddd”), type text),
    InsertWeekEnding = Table.AddColumn(InsertDayName, “WeekEnding”, each Date.EndOfWeek([Date]), type date),
    InsertWeekNumber= Table.AddColumn(InsertWeekEnding, “Week Number”, each Date.WeekOfYear([Date])),
    InsertMonthnYear = Table.AddColumn(InsertWeekNumber,”MonthnYear”, each [Year] * 10000 + [MonthOfYear] * 100),
    InsertQuarternYear = Table.AddColumn(InsertMonthnYear,”QuarternYear”, each [Year] * 10000 + [QuarterOfYear] * 100),
    ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{“QuarternYear”, Int64.Type},{“Week Number”, Int64.Type},{“Year”, type text},{“MonthnYear”, Int64.Type}, {“DateInt”, Int64.Type}, {“DayOfMonth”, Int64.Type}, {“MonthOfYear”, Int64.Type}, {“QuarterOfYear”, Int64.Type}, {“MonthInCalendar”, type text}, {“QuarterInCalendar”, type text}, {“DayInWeek”, Int64.Type}}),
    InsertShortYear = Table.AddColumn(ChangedType1, “ShortYear”, each Text.End(Text.From([Year]), 2), type text),
    AddFY = Table.AddColumn(InsertShortYear, “FY”, each “FY”&(if [MonthOfYear]>=FYStartMonth then Text.From(Number.From([ShortYear])+1) else [ShortYear]))
    in
    AddFY
    in
    fnDateTable

    Reply ↓

    • there are actually plenty of solutions out there which show how to calculate ISO Week in PoerQuery/M
      https://www.reddit.com/r/excel/comments/c4odzu/power_query_m_iso_week_iso_8601/
      http://excel-inside.pro/blog/2018/03/06/iso-week-in-power-query-m-language-and-power-bi/
      http://datacornering.com/how-to-calculate-iso-week-number-in-power-query/

      this is the actual code from the first link above:

      let
    StartDate        = #date (2009,1,1),
    EndDate          = #date (2024,12,31),
    ListOfDates      = List.Dates(StartDate, DurationDays, #duration(1, 0, 0, 0)),
    DurationDays     = Duration.Days (EndDate - StartDate) + 1,
    TableOfDates     = Table.FromList(ListOfDates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    DateColText      = Table.RenameColumns(TableOfDates,{{"Column1", "Date"}}),
    DateCol          = Table.TransformColumnTypes(DateColText,{{"Date", type date}}),
    WeekdayCol       = Table.AddColumn(DateCol, "Weekday Number", each Date.DayOfWeek([Date], Day.Monday) + 1,Int64.Type),
    IsoWeekYearCol   = Table.AddColumn(WeekdayCol, "Iso Week Year", each Date.Year(Date.AddDays([Date], 4-[Weekday Number])), Int64.Type),
    IsoWeekNrCol     = Table.AddColumn(IsoWeekYearCol, "Iso Week Nummer", each (Duration.Days(Date.AddDays([Date], 4-[Weekday Number]) - #date([Iso Week Year], 1 , 7 - Date.DayOfWeek(#date([Iso Week Year],1,4), Day.Monday)))/7)+1, Int64.Type),
    IsoWeekdayExtCol = Table.AddColumn(IsoWeekNrCol, "Iso Weekday Extended", each Text.From([Iso Week Year]) & "-W" & Text.End("0" &     Text.From([Iso Week Nummer]), 2) & "-"&  Text.From([Weekday Number]))
in
    IsoWeekdayExtCol
      Reply ↓

  17. Pingback: powerbi - Different approaches to calculate ISO year | ITTone

Leave a Reply

Your email address will not be published. Required fields are marked *

*