How to create relative week column in Power BI?

The problem

Time Intelligence is often a major topic of many BI implementations. Also in small self-service projects like Power BI one requirement is often to compare the devolpment of a KPI over time. For example, year over year or quarter to date. These can be implemented  easy in DAX for year, quarter and month. It has been written so much about These topic, I don’t want to bore you. For weekbased calculation there is no out of the box DAX forumular. One often used construct is the relative week, which shows the relative position of a week compared to the current week. Table 1 shows an example. The current week got the value 0. The following week 1 and the previous week -1.

If you want to filter the last 4 weeks, you can set a filter condition like „where RelativWeek <0 and RelativWeek >= -4“.

There are serval ways to create a relative week column in Power BI. To be honest this problem can bes solved very easy via SQL (Datediff(ww, getDAte(), Dateadd(dd,-1,[fullDate]))). But it is also possible to do this with Power Query or DAX. In this blogpost I will show you two solutions with Power Query and DAX in Power BI Desktop.

Create a relative week column with Power Query:

Let’s start with an empty Power BI project. You have to be familar with Power Query, otherwise I’m afraid that you won’t be able to follow every step. If you are a beginner, maybe this is the right moment to dive a little bit deeper into Power Query. You can download the complet Power BI project here RelativWeekProject.

1. We create a new empty Query in Power Query
2. We create a list of date with the function List.Dates
3. After that we transform the list into a table and rename the date column to “Date”
4. We create a custom column and use this M formular Date.Year([Date])*100+Date.WeekOfYear([Date]) , which will create a yearweek like 201601.
5. Now we remove all duplicate values from the Column yearWeek, so only unique year week combination remains.
6. We add an index column and rename it to WeekCounter
7. We add a new custom column (IsCurrentWeek) and enter the following forumalr Date.IsInCurrentWeek([Date]). This expression returns true if yearWeek is the current week.
8. We call this Table WeekCount
   

   

9. Now we create a new Query (DimDate), and do again step 1-4
10. Now we do a left join, we join DimDate[yearWeek] on WeekCount[yearWeek]
11. We expand the column WeekCounter and IsCurrentWeek. Now we have a running week counter for every day to the corresponding week. Now we can create the relative week counter
12. We add a new custom column (CurrentWeek) Table.SelectRows(WeekCount, each ([IsCurrentWeek] = true)){0} . After expanding the column, we will have the current week in every row of the table
    

    

13. The last step. We add again a custom column which will subtract [WeekCount]-[CurrentWeekRelativ]

So that’s it, every time we refresh our dataset this column will dynamically refreshed. Now we can build dynamic reports in Power BI, which only show the last or the next x weeks. This pattern can easy be adopted for other scenarios like years, quarter etc.

Create a relative week column in DAX

I use the exiting DimDate and the existing [yearWeek] columns from the previous Power Query script. You can also use your own date table for this formula.

1. Create a calculated column [DAX.WeekCount]

2. We create a second calculated colum

DAX.RelativWeek does the same as Power Query Step(13). The DAX variant is much easier to build, but can be a little bit confusing for anyone who is not familiar with DAX. The Power Query is much easier to understand if you understand the basic operation of table manipulation and joins.

Actually the Power Query version is slightly faster, because the Vertipaq-Engine can do a better compression with a column which is set during data load. The DAX generated columns will be created during processing the model. This has two negative impacts. First the compression won’t be as good and second every time the cube is processed, for example when a new measure is created, Power BI Desktop will recreate the two columns, which will take more time.

You can use Dax.RelativWeek or RelativWeek column as a filter in Power BI. Have fun.

Update:

In the comment section Imke, build an easyier forumalr to create the relativ week problem in Power Query. Actually this forumalar can even be parsed to DAX.  To get a realtiv week column in Power Query simply add after step 3 a custom column with the following M Code:

In DAX the forumalr looks similar, but you have to to do the start of week calculation by hand.

Thanks to Imke for this more elegant way.