Custom Week Grouping Made Easy in DAX

In Power BI, grouping and analyzing data by various time periods, such as weeks, months, and years, is a common practice. However, the default week grouping might not always suit your specific requirements. In this tutorial, we will show you how to create a custom calculated column that groups dates by week in a particular format: Week of the month, month name, and year. We will also discuss scenarios where a week spans across two months or even two years.

We will use the well-known Adventureworks data model, which you can download from https://github.com/microsoft/powerbi-desktop-samples/tree/main/DAX.

The WEEKNUM() function in DAX returns the week number in a year, ranging from 1 to 53. However, it is difficult to determine the month a week belongs to using this function. To make this more comprehensible, we will present the weeks in a more meaningful format, like this:

Using this format, it is much easier to understand that the 27th week of 2018 is the second week of July.

The DAX script for creating this custom calculated column is as follows:

Week = 
VAR CurrentDate = 'Date'[Date]
VAR CurrentMonth = FORMAT(CurrentDate, "MMM")
VAR CurrentYear = FORMAT(CurrentDate, "YY")
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR WeekEnd = WeekStart + 6

VAR IsWeekInTwoMonths = (MONTH(WeekStart)   MONTH(WeekEnd)) || YEAR(WeekStart)  YEAR(WeekEnd)
VAR IsWeekInTwoYears = YEAR(WeekStart)  YEAR(WeekEnd)

VAR FirstDayOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR FirstWeekStart = FirstDayOfMonth - WEEKDAY(FirstDayOfMonth, 2) + 1

VAR WeekOfMonth = DIVIDE(DATEDIFF(FirstWeekStart, WeekStart, DAY), 7) + 1

VAR PrevMonth = FORMAT(WeekStart, "MMM")
VAR FollowingMonth = FORMAT(WeekEnd, "MMM")

VAR WeekLabel =
    IF(
        IsWeekInTwoMonths,
        IF(
            IsWeekInTwoYears,
            "W 1 " & PrevMonth & " " & RIGHT(YEAR(WeekStart), 2) & "/" & FollowingMonth & " " & RIGHT(YEAR(WeekEnd), 2),
            "W 1 " & PrevMonth & "/" & FollowingMonth & " " & CurrentYear
        ),
        "W " & WeekOfMonth & " " & CurrentMonth & " " & CurrentYear
    )

RETURN
WeekLabel

The DAX script contains several variables and calculations that generate the desired output for the “Week” column. The script consists of the following steps:

1. Define the current date, month, and year using the FORMAT function for the month and year.
2. Calculate the start and end dates of the current week, with Monday as the starting day.
3. Determine if the current week spans across two months or two years.
4. Calculate the first day of the current month and the start date of the first week of the month.
5. Determine the week of the month.
6. Calculate and format the month when the week spans across two months or two years.
7. Format the week label based on whether the week spans across two months or two years.

Note that when a week spans across two months or two years, the script assigns “1” as the week number within that year or month.

Since the “Week” column is a text column, Power BI will sort it alphabetically. To sort the weeks correctly, create an additional calculated column called “Week order”:

Week order = 
VAR CurrentDate = 'Date'[Date]
VAR CurrentMonth = MONTH(CurrentDate)
VAR CurrentYear = YEAR(CurrentDate)
VAR WeekStart = CurrentDate - WEEKDAY(CurrentDate, 2) + 1
VAR WeekEnd = WeekStart + 6

VAR IsWeekInTwoMonths = (MONTH(WeekStart) <>  MONTH(WeekEnd)) || YEAR(WeekStart) <> YEAR(WeekEnd)
VAR IsWeekInTwoYears = YEAR(WeekStart) <> YEAR(WeekEnd)

VAR FirstDayOfMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)
VAR FirstWeekStart = FirstDayOfMonth - WEEKDAY(FirstDayOfMonth, 2) + 1

VAR WeekOfMonth = DIVIDE(DATEDIFF(FirstWeekStart, WeekStart, DAY), 7) + 1

VAR PrevMonth = FORMAT(WeekStart, "MMM")
VAR FollowingMonth = FORMAT(WeekEnd, "MMM")

VAR WeekLabel =
    IF(
        IsWeekInTwoMonths,
        IF(
            IsWeekInTwoYears,
            YEAR(WeekEnd)*1000+MONTH(WeekEnd)*10+1,
            CurrentYear*1000+ MONTH(WeekEnd)*10+1
        ),
        CurrentYear*1000+CurrentMonth*10+WeekOfMonth
    )

RETURN
WeekLabel

In this script, multiply the year by 1000, the month by 10, and then add the week number to obtain a whole number that sorts the weeks correctly. This method originates from SQL.

Finally, sort the “Week” column by the “Week order” column.