locked
Enhancement request: ISO week number RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    6
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

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

    Regards,
    M.

    Sunday, April 6, 2014 6:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

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

    Regards,
    M.

    Sunday, April 6, 2014 6:38 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote
    Here is the link to vote for this idea: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13507155-iso-week-number-option-on-date-weekofyear-function
    Wednesday, January 18, 2017 9:22 AM