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.

215 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    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
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      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.