How can we improve Power BI?

ISO week number option on Date.WeekOfYear function

It would be really convenient to align the M function Date.WeekOfYear to the Excel WEEKNUM function to return a ISO 8601 compliant week number. Time intelligence properties are the starting point for a plethora of other calculations. And this "tile" is missing.

226 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

14 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • Maurizio Loffredo commented  ·   ·  Flag as inappropriate

    Hi All,

    After waiting for ages, I realized that "every man for himself" would have worked much better than Microsoft Engineers.

    So, based on some comments (herein, thanks guys) which refer to some useful web resources, the best way to get this is by creating a lean, separate, custom function to Invoke into your calendar table when adding the ISO Week Column.

    Here it is:

    let
    ISO8601Week = (Date as date) =>
    let
    AncillaryWeek = (Date as date) =>
    let
    WeekDay = 1 + Date.DayOfWeek(Date, Day.Monday),
    OrdinalDay = Date.DayOfYear(Date),
    AncillaryWeekNumber = Number.RoundDown((OrdinalDay - WeekDay + 10) / 7)
    in
    AncillaryWeekNumber,

    ThisYear = Date.Year(Date),
    PriorYear = ThisYear - 1,
    AncillaryNumber = AncillaryWeek(Date),
    LastWeekOfPriorYear = AncillaryWeek(#date(PriorYear, 12, 28)),
    LastWeekOfThisYear = AncillaryWeek(#date(ThisYear, 12, 28)),
    WeekNumber = if AncillaryNumber < 1 then LastWeekOfPriorYear else
    if AncillaryNumber > LastWeekOfThisYear then 1 else AncillaryNumber
    in
    WeekNumber
    in
    ISO8601Week

  • Anonymous commented  ·   ·  Flag as inappropriate

    The implementation of the github function (https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca) may be a little daunting. Here's code to generate a DateDim with the modified gethub function:

    /*
    based on <https://en.wikipedia.org/wiki/ISO_week_date#Calculating_the_week_number_of_a_given_date>
    M / Power Query doesn't have a native ISO8601 Week Number function, and DAX's
    `weeknum(x, 21)` doesn't give the correct ISO Week-Year.

    homepage: <https://gist.github.com/r-k-b/18d898e5eed786c9240e3804b167a5ca>

    */

    let
    getISO8601Week = (someDate as date) =>
    let
    getDayOfWeek = (d as date) =>
    let
    result = 1 + Date.DayOfWeek(d, Day.Monday) //DPS - Use this to change First Day of week to Sunday: Date.DayOfWeek(d, Day.Sunday)
    in
    result,

    getNaiveWeek = (inDate as date) =>
    let
    // Sunday = 1, Saturday = 7
    weekday = getDayOfWeek(inDate),
    weekdayOfJan4th = getDayOfWeek(#date(Date.Year(inDate), 1, 4)),
    ordinal = Date.DayOfYear(inDate),
    naiveWeek = Number.RoundDown(
    (ordinal - weekday + 10) / 7
    )
    in
    naiveWeek,
    thisYear = Date.Year(someDate),
    priorYear = thisYear - 1,
    nwn = getNaiveWeek(someDate),
    lastWeekOfPriorYear =
    getNaiveWeek(#date(priorYear, 12, 28)),

    // http://stackoverflow.com/a/34092382/2014893
    lastWeekOfThisYear =
    getNaiveWeek(#date(thisYear, 12, 28)),

    weekYear =
    if
    nwn < 1
    then priorYear
    else
    if nwn > lastWeekOfThisYear
    then thisYear + 1
    else thisYear,

    weekNumber =
    if nwn < 1
    then lastWeekOfPriorYear
    else
    if nwn > lastWeekOfThisYear
    then 1
    else nwn,

    // Padded version: week_dateString = Text.PadStart(Text.From(Number.RoundDown(weekNumber)),2,"0")
    week_dateString = weekNumber
    in
    // Text.From(weekYear) & "-W" & week_dateString & "-" & Text.From(getDayOfWeek(someDate)),
    week_dateString,
    /* let */
    StartDate = #date(StartYear,1,1),
    EndDate = #date(EndYear,12,31),
    NumberOfDays = Duration.Days( EndDate - StartDate ),
    Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateKey"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateKey", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateKey]), type number),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([DateKey]), type number),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateKey]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateKey]), type number),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateKey]), type number),
    #"Inserted Week of Year ISO" = Table.AddColumn(#"Inserted Week of Year", "Week of Year ISO", each getISO8601Week([DateKey]), type number),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year ISO", "Week of Month", each Date.WeekOfMonth([DateKey]), type number),
    #"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([DateKey]), type number),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([DateKey]), type number),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([DateKey]), type number),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([DateKey]), type text),
    #"Added Custom" = Table.AddColumn(#"Inserted Day Name", "YYWW", each Text.End(Number.ToText([Year]),2) & Text.End("0" & Number.ToText([Week of Year]),2)),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each true)
    in
    #"Filtered Rows"

  • Anonymous commented  ·   ·  Flag as inappropriate

    sorry, correction to the previous workaround. Date.WeekOfYear(Date.AddDays([date],-1)), the O of WeekOfYear is uppercase

  • Anonymous commented  ·   ·  Flag as inappropriate

    Workaround: Add a custom column with the formula "=Date.WeekofYear(Date.AddDays([date],-1))". Where [date] is your field with the date. The line in the AdvanceEditor looks like this "= Table.AddColumn(#"Changed Type", "NewColumn", each Date.WeekOfYear(Date.AddDays([Date],-1)))"

  • Hauke commented  ·   ·  Flag as inappropriate

    It just cost me quite some time to see that the M-function in Query-Editor, which is generated by
    Add Column - Date - Week - Week of Year
    really behanved different from the function "WEEKNUM".
    It really is solved quite satisfactory in Excel.
    It should REALLY by introduced into M, so that I can append tables with WEEK-comlumns efficiently in query designer.

  • Luc van de Plas commented  ·   ·  Flag as inappropriate

    That doesn't seem to fix the problem:

    Expression error: The name 'weeknum' wasn't recognized. Make sure it's spelled correctly.

    input given: =weeknum(date,21)

  • Maksym Koronenko commented  ·   ·  Flag as inappropriate

    Dear Jeffrey, unfortunatelly DAX function like M functions both returns incorrect week number, it's not ISO number

  • Roberto Campassi commented  ·   ·  Flag as inappropriate

    Thank you very much for your suggestion, Jeffrey.
    However, it would be much better to be able to reference a Power Query table cell for other interim evaluations and calculations... before loading all the needed structures in DAX and without writing kilometers of code only to obtain this result.

  • Jeffrey Wang commented  ·   ·  Flag as inappropriate

    If all you need is to add a column to a table, one option today is to add a calculated column using DAX WEEKNUM function that behaves identically to Excel's.

Feedback and Knowledge Base

Ready to get started?

Try new features of Power BI today by signing up and learn more about our powerful suite of apps.