WEEKNUM

How to Use the Power BI DAX function WEEKNUM

Syntax of the WEEKNUM Function

The syntax of the WEEKNUM function is as follows:


WEEKNUM(❰date❱, [❰return_type❱])


Here, `❰date❱` is the date for which you want to find the week number. The optional argument `❰return_type❱` specifies the type of week numbering system to be used. If `❰return_type❱` is not specified, the function uses the ISO week numbering system by default.

Examples of the WEEKNUM Function

Let's look at some examples of how to use the WEEKNUM function in Power BI.

Example 1: Using the Default ISO Week Numbering System

Suppose you have a table with a column `OrderDate` that contains dates in the format `dd/mm/yyyy`. To calculate the week number of each order, you can use the following DAX formula:


WeekNumber = WEEKNUM([OrderDate])


This formula calculates the week number of each `OrderDate` using the ISO week numbering system.

Example 2: Using the US Week Numbering System

In the US, the week starts on Sunday, and the week containing January 1st is considered the first week of the year. To calculate the week number of each order using the US week numbering system, you can use the following DAX formula:


WeekNumber_US = WEEKNUM([OrderDate], 1)


This formula calculates the week number of each `OrderDate` using the US week numbering system.

Example 3: Using the European Week Numbering System

In some European countries, the week starts on Monday, and the week containing the first Thursday of the year is considered the first week of the year. To calculate the week number of each order using the European week numbering system, you can use the following DAX formula:


WeekNumber_EU = WEEKNUM([OrderDate], 2)


This formula calculates the week number of each `OrderDate` using the European week numbering system.

Using the WEEKNUM Function in Visualizations

Once you have calculated the week number using the WEEKNUM function, you can use it in various visualizations in Power BI. For example, you can create a line chart showing the trend of sales by week, or a table showing the total sales for each week.

To create a line chart showing the trend of sales by week, you can use the following steps:

1. Add a line chart to your report.

2. Drag the `SalesAmount` column to the `Values` field.

3. Drag the `OrderDate` column to the `Axis` field.

4. Change the `OrderDate` column to show the week number instead of the date. To do this, right-click on the `OrderDate` column, select `New measure`, and enter the following DAX formula:


WeekNumber = WEEKNUM([OrderDate])


5. Drag the `WeekNumber` measure to the `Axis` field.

6. Customize the chart as required.

The WEEKNUM function in Power BI is a powerful tool for analyzing data by week. By using the function with different week numbering systems, you can handle various scenarios involving week-based analysis. We hope this article has given you a good understanding of how to use the WEEKNUM function in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)