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
