Analyzing time-based data in Power BI demands a keen understanding of trends and patterns, especially across weekly intervals. Fortunately, Power BI provides a seamless solution for this with its feature to effortlessly group data by week, allowing users to delve beyond daily fluctuations and uncover valuable insights.

This functionality proves invaluable when handling extensive datasets, enabling users to aggregate data into weekly intervals and gain clarity on weekly fluctuations, seasonal trends, and overall performance metrics.

Whether it pertains to sales figures, customer behavior, or project milestones, organizing data into weekly groupings fosters intuitive visualization and interpretation, empowering users to discern actionable insights and drive informed decisions with confidence.

Group by Week with a WeekByYear Column

The structure of your date table is crucial for filtering your model by date. To group measures by weeks, ensure your date table includes a dedicated week column. This can be easily added using DAX functions or M in Power Query.

You can have the week column set up so that it reflects the week number of each date within a year. This can be easily done using the WEEKNUM DAX function.

WEEKNUM ( <Date> [, <ReturnType>] )

WEEKNUM returns the week number of a given date. It indicates the week where a given date falls within the year. Its parameters are.

• Date – a date.
• ReturnType – this parameter dictates the system the function uses to identify the first week of the year. The WEEKNUM function utilizes two systems for this purpose. The default system designates the first week which includes January 1 as the initial week of the year.

In contrast, the second system aligns the first week of the year with the ISO 8601 standard, which defines it as the week with four or more days falling within the new year.

The ReturnType parameter also allows you to select the starting day of the week. The table below illustrates the values accepted by the parameters and their corresponding interpretations within the WEEKNUM function.

DateTable = 
    VAR MinYear = YEAR ( DATE ( 2010, 01, 01 ) )
    VAR MaxYear = YEAR ( DATE ( 2010, 12, 31 ) )
RETURN
    ADDCOLUMNS (
            CALENDAR (
            DATE ( MinYear, 01, 01 ),
            DATE ( MaxYear, 12, 31 )            
            ),
        
        "Year", YEAR ( [Date] ),
        "Month", MONTH ( [Date] ),
        "MonthName", FORMAT ( [Date], "mmmm" ),
        "WeekofYear", WEEKNUM ( [Date], 2 )
    )

This syntax uses variables to establish the date range and generate the date table using the ADDCOLUMNS function. The CALENDAR function returns the Date column, acting as the main date column from which other columns are derived.

When you copy and paste the syntax into the formula bar, you have a date table with year, month number, month name, and week of year.

This setup enables straightforward weekly data analysis. The visual depiction clearly illustrates a decline in sales within the chosen country as the year unfolded.

Group by WeekofMonth Parameter

The WeekofYear column in the first example tracks the week number from the start to the end of the year, enabling weekly event analysis. However, it falls short in visualizing event progression in shorter spans, such as monthly.

This section addresses this gap by detailing the creation of a WeekofMonth column, tallying weeks within a month and resetting at each new month’s start.

WeekofMonth = 

VAR WeekEnding = [Date] - WEEKDAY( [Date], 2 ) + 7
VAR MonthEnd = EOMONTH( WeekEnding, -1 ) + 1

RETURN
1 + WEEKNUM( WeekEnding, 2 ) - WEEKNUM( MonthEnd, 2 )

This syntax creates values for the WeekofMonth column. Here’s a breakdown of the syntax.

WeekEnding = [Date] – WEEKDAY( [Date], 2 ) + 7 – this variable returns the date of the end of the week for a given date.

The most important part of the WeekEnding variable is the WEEKDAY syntax.

WEEKDAY ( <Date> [, <ReturnType> )

The WEEKDAY function returns a number that identifies the day of the week. This number can range from 1-7 or 0-6 as determined by the ReturnType argument.

The ReturnType argument takes 1, 2, or 3. The table above explains how the WEEKDAY interprets each value.

In the WeekEnding variable, the WEEKDAY syntax returns the number corresponding to the day of the week for the given date, with the week starting on Monday.

So, assuming the [Date] = 18-Feb-2024, the WEEKDAY syntax will be;

WEEKDAY(18-Feb-2024, 2) = 7 (18-Feb-2024 is a Sunday because the ReturnType parameter is 2 which means the week begins on Monday and ends on Sunday; hence, 7)

Now, what the WeekEnding variable will be as follows;

WeekEnding = 18-Feb-2024 – 7 + 7 = 18-Feb-2024 (Sunday).

The WeekEnding variable helps you find the end date of a week for a specific date.

MonthEnd = EOMONTH( WeekEnding, -1 ) + 1 – This variable returns the date of the first day of the month for any given date. The variable uses the EOMONTH function for this.

EOMONTH ( <StartDate, <Months> )

EOMONTH returns the last day of the month before or after a specified number of months. In the MonthEnd variable, the EOMONTH syntax returns the last day of the month for the given date – the date returned by the WeekEnding variable.

The output is then incremented by 1 to return the first day of the month for the given date.

From our previous example, WeekEnding returned 18-Feb-2024. Based on this, the MonthEnd variable will return 01-Feb-2024.

• 1 + WEEKNUM( WeekEnding, 2 ) - WEEKNUM( MonthEnd, 2 ) – this syntax returns the number of the week where a specific date falls.
• WEEKNUM( WeekEnding, 2 ) – this returns the week number of the date returned by the WeekEnding variable.
• WEEKNUM( MonthEnd, 2 ) – this returns the week number of the first day of the month which is the value returned by the MonthEnd variable.

Basically, the syntax takes the week number of the last week date and subtracts it from the week number of the first day of the month. Then, 1 is added to this result to find the week number of a specific date.

Paste the syntax into your formula bar to add a new column to your date table. You’ll observe that the numbers in the WeekofMonth and WeekofYear columns are quite alike until the end of January approaches, at which point they begin to diverge.

Now you can enhance your analysis by identifying performance on a weekly basis within the month.

Group by Week Using Fiscal Date Table

Up to now, the weekly grouping has followed the natural calendar, yet opting for fiscal week grouping could prove more beneficial.

Fiscal calendars provide a more insightful analysis timeframe compared to calendar weeks because they sync with an organization’s financial reporting cycles. By grouping data by fiscal weeks, you can summarize your data, pinpoint trends, and derive informed insights from these significant periods.

To group by fiscal weeks, you first need to create a fiscal calendar. You can read all about creating a fiscal calendar in the article.

Using the Fiscal Week Number column, you can derive the fiscal week number of a fiscal month using the syntax below.

FiscalWeekofMonth = 
SWITCH(
    TRUE(),
    DateTable[Fiscal Week Number] <= 4, DateTable[Fiscal Week Number],
    DateTable[Fiscal Week Number] <= 52,
        DateTable[Fiscal Week Number] -
        FLOOR(
            (DateTable[Fiscal Week Number] - 1) / 4, 
            1
        ) * 4
)

The syntax is built around two important DAX functions – SWITCH and FLOOR.

SWITCH( <expression>, <value>, <result>[, <value>, <result>]…[, <else>] )

• expression – the value or calculation that will be the basis of comparison.
• value (repeatable) – a series of values you want to compare against the expression.
• result (repeatable) – the output values to be returned if the expression matches a corresponding value. There should be a result for each value.
• else (optional) – a default output to be returned if the expression fails to match any of the specified values.

The SWITCH function eliminates the need for lengthy and complex nested IF statements. By using the SWITCH function, you simply specify the expression and the desired outcomes when the expression fulfils specific conditions.

FLOOR ( <number>, <significance> )

• number – the number you want to round.
• significance – the multiple you want to you want to round the number.

The FLOOR function is designed to round a given number downwards (towards zero) to the closest multiple of a specified value (the significance).

Here’s an explanation of the syntax;

• SWITCH(TRUE(), ...) – this function evaluates multiple conditions provided after the comma. It evaluates each condition in order and returns the result of the expression associated with the first condition that evaluates to TRUE. In this context, TRUE() simply acts as a placeholder condition that always evaluates to TRUE. It’s used to trigger the evaluation of subsequent conditions.
• DateTable[Fiscal Week Number] <= 4 – this condition checks if the fiscal week number is less than or equal to 4. If this condition is met, the corresponding expression DateTable[Fiscal Week Number] is returned as the result.
• DateTable[Fiscal Week Number] <= 52 – this condition checks if the fiscal week number is less than or equal to 52. This ensures that the subsequent logic applies to all fiscal weeks.
• DateTable[Fiscal Week Number] - FLOOR((DateTable[Fiscal Week Number] - 1) / 4, 1) * 4 – this expression calculates the fiscal week of the month. It subtracts an adjustment from the fiscal week number based on the floor division of (DateTable[Fiscal Week Number] – 1) / 4, where the divisor 4 represents the number of weeks in a month. The adjustment ensures that the fiscal week within the month resets after every 4 weeks.

After pasting the syntax into the column, you will establish a fiscal week numbering system based on a 4-week cycle. This can be used for your analysis purposes.

Conclusion

Grouping data by week provides a straightforward yet powerful method for analyzing time-based trends and patterns within large datasets. By organizing data into weekly intervals, you gain valuable insights into fluctuations, seasonal trends, and overall performance metrics with greater clarity that will otherwise go unnoticed.

Analyzing sales figures, monitoring customer behavior, or tracking project milestones—all benefit from the ability to group data by week. It not only enhances visualization and interpretation but also helps you to make informed decisions and drive strategic initiatives effectively.

By mastering these techniques discussed above for grouping data by week in Power BI, you too can unlock deeper insights into the time-based patterns within your datasets.