none
Enhancement request: ISO week number RRS feed

  • Question

  • M language includes a function that returns the week number from a date: Date.WeekOfYear

    However, it is much less flexible than the Excel 2010 Worksheet function WEEKNUM, which accepts a "return type" parameter. It can be used to return a ISO 8601 compliant week number. Ex: WEEKNUM(date,21).

    It would be interesting to align the M function Date.WeekOfYear to the Excel WEEKNUM.

    For those confronted to the challenge of building with Power Query a time dimension that includes an ISO week number, the following M code implements the formula found in this blog. If you already have a column "Date", you can use the following code to add ISO year and week number columns:

        InsertISOYear = Table.AddColumn(previousstepname, "ISOYear", each Date.Year(Date.AddDays([Date],3-Date.DayOfWeek([Date],1))), type number),
        InsertRefDate = Table.AddColumn(InsertISOYear , "RefDate", each #date([ISOYear],1,3)),
        InsertISOWeek = Table.AddColumn(InsertRefDate , "ISOWeek", each Number.IntegerDivide(Duration.Days( [Date]-[RefDate])+Date.DayOfWeek([RefDate],0)+6,7), type number ),
        InsertWeekInt = Table.AddColumn(InsertISOWeek , "WeekInt", each [ISOYear] * 100 + [ISOWeek], type number),
        InsertCalendarWeek = Table.AddColumn(InsertWeekInt , "WeekInCalendar", each Number.ToText([ISOYear]) & Number.ToText([ISOWeek],"-W00")),
        RemovedColumns = Table.RemoveColumns(InsertCalendarWeek ,{"RefDate"}) 
    

    Wednesday, April 2, 2014 11:58 AM

Answers

  • Thanks for your feedback. We will consider this for future enhancements to this function.

    Regards,
    M.

    Sunday, April 6, 2014 6:38 PM
  • Thanks everyone for the feedback! Please file this request in our UserVoice forum so we can prioritize it according to the volume of requests.

    UserVoice Forum for Data Preparation: https://ideas.powerbi.com/forums/265200-power-bi-ideas/category/161721-data-preparation

    Thanks,
    M.

    Tuesday, April 19, 2016 6:36 PM

All replies

  • Thanks for your feedback. We will consider this for future enhancements to this function.

    Regards,
    M.

    Sunday, April 6, 2014 6:38 PM
  • I ran into the same issue, but had problems copying your solution. However, I think there is a quite simple alternative by using the Date.AddDays function. So instead of using e.g. Table.WeekOfYear([Date]) use Table.WeekOfYear(Date.AddDays([Date],-1)). That substracts one day from the date and gives the Monday-Sunday weeknumber.
    Tuesday, January 20, 2015 11:34 AM
  • The issue is not on which day a week starts, but which week is week 1 of the year. In the ISO definition, January 1 is not always in week 1 but could be in week 53 of the previous year. It's annoying that the standard weeknumber functions are unusable for a large part of the world, but the workaround is fairly simple. The ISO definition says that January 4 as well as the first Thursday in a year alway are in week 1. So what you do is to take a date and calculate the distance in weeks to Jan 4 or the first Thursday.
    Wednesday, January 20, 2016 7:41 AM
  • I join the enhancement request.

    It would be definitely convenient having this feature implemented (and aligned with Excel).

    Time intelligence properties are the starting point for a plethora of other calculations. And this "tile" is missing.

    Tuesday, April 19, 2016 3:10 PM
  • Thanks everyone for the feedback! Please file this request in our UserVoice forum so we can prioritize it according to the volume of requests.

    UserVoice Forum for Data Preparation: https://ideas.powerbi.com/forums/265200-power-bi-ideas/category/161721-data-preparation

    Thanks,
    M.

    Tuesday, April 19, 2016 6:36 PM