Returns week number for a given date column.
Tutorial with example
In today’s video we will be covering VALUE, YEAR and WEEKNO, to create a dashboard that shows data from the previous week. It is easier than you think, but if your weeks don’t start on Sunday and you have a week number column that you need to sort using a fiscal calendar, then it gets a bit trickier to do that.
Download example file:
Download Power BI file: here.
In contrast to Microsoft Excel, which stores dates as serial numbers, DAX uses a datetime data type to work with dates and times. If the source data is in a different format, DAX implicitly converts the data to datetime to perform calculations.
By default, the WEEKNUM function uses a calendar convention in which the week containing January 1 is considered to be the first week of the year. However, the ISO 8601 calendar standard, widely used in Europe, defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are different from the ISO 8601 definition.