WhatTheFact_Logo_Transparent_353x76px

Easy week over week comparison in Power BI with the new OFFSET function

Short-Link: đź”— https://wtf.bi/weekcomparison

This post is also available in: Deutsch

In data analysis a common requirement is to compare your values of one week to the ones of the previous week. Such a week over week comparison gives important insights as you compare a bigger timeframe and can – without waiting for months – still see where the trend is going.

How to handle the change of the year

Usually you can just take the measure in a specific timeframe like the current week, for example week 28 of the current year 2023, and compare it to the previous week, in this case week 27 of 2023. However, such a comparison becomes challenging, as you have to handle the change of years. Somehow you have to manage that week 1 in 2023 suddenly compares to week 52 in 2022.
And this is also the biggest challenge in week over week comparisons. You just cannot compare to the last week as if it would be a continuous number.

New approach with window function OFFSET

In the past there were a few approaches on how you can solve this issue, but all of them were kind of complex. However with the introduction of the new window functions, we can use now an approach that is a lot simpler.

As mentioned already, we are going to use the new OFFSET function. The OFFSET function returns a row relative to the current one. And that is exactly what we need here. We want to have the value from the last row (= previous week) compared to the current one.

As a general example for the OFFSET function, I use a simple table and display the brands and the sales amount. If now I use the OFFSET function in a new measure, I can get the value relative from the current position. In the following example I defined that I want the previous item (-1) by brand. So the table looks like this and I always get the data from the last row in the measure OFFSET Brand:

OFFSET function in DAX
OFFSET function in DAX

The syntax is very simple as we just use CALCULATE to change the filter context and use OFFSET as modifier. We define how big the offset should be and by what the offset should happen. So the measure OFFSET Brand looks like this:

OFFSET Brand = 
CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY( 'Product'[Brand], ASC )
    )
)

Be aware that at the time of writing this article, the syntax highlighter in Power BI doesn’t fully support the new window functions. So although the measure is correct, there might be some red lines in the editor:

OFFSET function is not fully supported in DAX editor
OFFSET function is not fully supported in DAX editor

Hopefully by the time of reading this article, the syntax highlighter is fully supporting the window functions.

Create a column for the full week

In order to implement the new approach, we simply have to have a column in our data model with the week and year for each day. This column we will use for the sort for the offset to get the previous week.

Be aware that we want to compare whole weeks. For that reason we have to add a column that is using the ISO 8601 standard that is common Europe. The normal US- and Australian standard won’t work as it can have half weeks by the change of the year. Take a look at the article “Calculate ISO 8601 week in DAX” where I describe the whole szenario.

Here the quick explanation.

In the US-calendar the week of January 1st is always week 1 of the new year. This we cannot use as we would compare week 2 with 7 days, to week 1 with only 3 days in 2021. Also week 53 in 2020 would only have 4 days. So this calendar isn’t useful as it’s not comparing whole weeks:

Week calculation in North America and Australia with the start of 2021 as example
Week calculation in North America and Australia with the start of 2021 as example

The ISO 8601 calendar is dealing with whole weeks. Accordingly we can use that as we always have full weeks to compare:

Week calculation with the ISO 8601 calculation with the start of 2021 as example
Week calculation with the ISO 8601 calculation with the start of 2021 as example

For that reason we are going to create a new column as combination of the year and the week number. We can easily do that in DAX with the following calculated column:

Year Week = 
VAR _YearCurrentRow = YEAR ( 'Date'[Date] )
VAR _MonthCurrentRow = MONTH( 'Date'[Date] )
VAR _WeekCurrentRow = WEEKNUM ( 'Date'[Date], 21 )
VAR _YearReturn = IF( _WeekCurrentRow > 50 && _MonthCurrentRow = 1, _YearCurrentRow -1, _YearCurrentRow )
RETURN
    _YearReturn * 100 +_WeekCurrentRow

Be aware of row 5 with the variable _YearReturn. We have to make sure that the first days of the new year still get the year and week of the old year. Like in the last picture, in 2021 January 1st to 3rd are by ISO norm still in week 53 of 2020.

Like this, our year and week column will look like this:

image 2

Use OFFSET function to get the previous weeks values

Once we created this column, the rest is pretty easy. We can now use CALCULATE to change the filter context and use OFFSET to get the previous week:

Sales Previous Week = 
CALCULATE(
    [Sales Amount],
    OFFSET(
        -1,
        ORDERBY( 'Date'[Year Week], ASC )
    )
)

From then on we get the values from the previous week with the new measure:

image 3

This also means from then on it’s pretty easy to do all kind of measures that consider a week over week comparison. For example we can now easily calculate the week over week change in percent:

Week over Week in % = 
DIVIDE( ([Sales Amount] - [Sales Previous Week] ) , [Sales Previous Week] )

Like this we get the change from week to week in percent:

image 4
Week over week in percent

Summary week over week with OFFSET

We know that there is a challenge with the week over week comparison as we somehow have to handle the change of the years. With a proper week column in the ISO format we can easily create an order for all of the weeks and over multiple years.

Afterwards we can use the new OFFSET function to always get the last element as the last week value. This includes the change of year, as we always get the correct previous element with this approach.

Download Example file

Share:

LinkedIn
Twitter
WhatsApp
Telegram
Email
Print
Denis Selimovic

Denis Selimovic

As a user from the very beginning, Denis Selimovic is passionate about Power BI and everything related to it. In his blog WhatTheFact.bi, he writes about the latest developments in Power BI and provides tips and tricks on the subject. Besides being a Power BI enthusiast, blog author, speaker and principal consultant, he is also co-organizer of the Meetup Power BI User Group Switzerland.

Kommentare:

3.5 2 votes
Article Rating
Subscribe
Notify of
guest
5 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Jeevan A Y
Jeevan A Y
2 months ago

Nice article.. Please leave us the PBI practice file

Jeevan A Y
Jeevan A Y
1 month ago

Thank you Denis, this will help us a lot.

Peter
Peter
6 days ago

Thanks for sharing, I have a question though.
I have tried to implement the solution listed under the ‘Use OFFSET function to get the previous weeks values’ section and PowerBI tells me that the parameter within the OFFSET function is not of the correct type.I have the same integer Year-Week column, so I’m not sure why my PowerBI thinks this is not the correct type.
I don’t see this error in Tabular Editor, only when viewing the measure logic in the .pbix file itself.

I understand from your post that PowerBI might not recognise this as being a correct function, but the measure doesn’t display any data either when place into a table.

Any ideas what might be going on there?

5
0
Would love your thoughts, please comment.x
()
x
Wait!

Follow me on LinkedIn and you’ll gain access to valuable insights, expert tips, and industry updates related to Power BI. Stay ahead of the curve and supercharge your analytics skills today!

Supercharge your Power BI skills!