WEEKNUM DAX Function in Power BI

The WEEKNUM function in DAX returns the week number of a given date.
• By default, it considers January 1st as the start of week 1.
• We can also specify which day of the week is considered the first day (Sunday or Monday).

DAX Syntax WEEKNUM(date[, return_type])

The function has the following parameters:

date: A valid date (can be a column with dates, or a single date value).
return_type: It is an optional parameter.
- Defines which day the week starts on.
- If omitted → defaults to 1 (week starts on Sunday).

Example: Let’s create a calculated column in Power BI.

DAX

Weeknum Measure = WEEKNUM(Sales[Order Date],1)

The output of the above code is shown below:

Example: Adding Year + Week for reporting. We can combine year and week number to create a unique identifier. This is useful for chronological sorting and time intelligence calculations.

DAX

YearWeek = YEAR('Sales'[Order Date]) * 100 + WEEKNUM('Sales'[Order Date], 2)

The output of the above code is shown below:

Note: At the start of every year, the week number resets back to 1, even if the new year started mid-week.

Example: Weekly Sales Aggregation. To calculate total sales per week.

DAX

Weekly Sales =
CALCULATE(
    SUM('Sales'[Sales]),                     // Step 1: Sum of Sales for the current context
    ALLEXCEPT('Sales', 'Sales'[YearWeek])    // Step 2: Keep only YearWeek context, ignore other filters
)

Example: Rolling 4-Week Average. Instead of monthly, we may want rolling weekly averages.

DAX

Rolling 4 Week Avg =
AVERAGEX(
    DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -28, DAY),
    CALCULATE(SUM('Sales'[SalesAmount]))
)

Gives moving average across last 4 weeks.

Working of the code: • Iterates over each date in the 28-day window.
• For each date, it evaluates SUM(Sales[SalesAmount]).
• Then it averages all those daily sales values.

Result = Average daily sales over the last 28 days.

Previous Next