10.02.2016

How to create relative week column in Power BI?

Technical Value

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. Relative Week Table

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
    WeekCount Table build with Power Query in Power BI Desktop
    WeekCount Table build with Power Query in Power BI Desktop
  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
    Our new DimDate with the current week in a seperat column
    Our new DimDate with the current week in a seperat column
  13. The last step. We add again a custom column which will subtract [WeekCount]-[CurrentWeekRelativ]
A dimDate with a relative week column
A dimDate with a relative week column

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]

  1. DAX.WeekCounter = CALCULATE(DISTINCTCOUNT(DimDate[yearWeek]);all(DimDate);DimDate[yearWeek]<=EARLIER(DimDate[yearWeek]))

2. We create a second calculated colum

  1. RelativWeek = DimDate[DAX.WeekCounter]-CALCULATE(sum(DimDate[DAX.WeekCounter]);ALL(DimDate);TODAY()=DimDate[Date])
Relative week created by DAX
Relative week created by DAX

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:

  1. (Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/7

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

  1. Var StartOfWeek= DimDate[Date]- WEEKDAY(DimDate[Date];2)+1
  2. Var StartOfCurrentWeek=TODAY()-WEEKDAY(TODAY();2)+1
  3. Return (StartOfWeek-StartOfCurrentWeek)/7

Thanks to Imke for this more elegant way.

Kommentare

Jimi
Do, 18.08.2016 - 21:35

Hello. I am trying to use the RelativWeek variable as a divisor. But PowerBI will not allow it. At our business a new hire should be completing 100 points (1 course) of training each week during their first 8 weeks of employment. So I am calculating their total score for all courses and trying to divide by the amount of weeks they have been employed. Unfortunately Power BI does not allow me to do this. Any ideas what I can do to accomplish this task?

Screenshot -- https://i.imgur.com/16C20jk.png

Lötters
Lukas
Lötters
Do, 25.08.2016 - 13:00

Hi Jimi,

wouldn't it be enought to add 1 to the relativ week Count and then divid your score by the new relativweek counter ? If you have problems with Zero Division try out the divide forumalr of DAX. I think tihs would be the easiest way.

Lukas

Angel
Do, 20.10.2016 - 14:43

Hi,

I have tried DAX method and column WeekCounter has always value = 1.

The column is defined as follows:

WeekCounter = CALCULATE(DISTINCTCOUNT(Calendario[YearAndWeek]);all(Calendario);Calendario[YearAndWeek]=EARLIER(Calendario[YearAndWeek]))

Am I doing something wrong?.

Thanks,

Lötters
Lukas
Lötters
Do, 20.10.2016 - 14:58

Hi Angel,

in your Formular misses the &lt; sign.Without it the filter limits the number of returned columns to 1 (Where the YearAndWeek in the current week is equal to YearAndWeek)

WeekCounter = CALCULATE(DISTINCTCOUNT(Calendario[YearAndWeek]);all(Calendario);Calendario[YearAndWeek]&lt;=EARLIER(Calendario[YearAndWeek]))

Hope it works now.

Lukas

Imke Feldmann
Mi, 10.02.2016 - 17:51

Like the walkthrough-solution for Power Query, but in defense of M I’d like to say that there is a formula-solution as well. Actually, you even don’t need the week counter. Just add a custom column after your StepYearWeek like this:
[YearWeek]-Table.SelectRows(StepYearWeek, each [Date]=Date.From(DateTime.LocalNow()))[YearWeek]{0})

Lötters
Lukas
Lötters
Mi, 10.02.2016 - 18:15

Hi Imke,
thanks for your Reply. I fear you formular has a little problem. Please correct me if I'm wrong. Your forumlar simply substract every YearWeek by the current YearWeek. For only one year this works fine. But if the current week is let's say 201701, then you have a huge gap between the last week of 2016 and the first of 2017 (201652-201701=-49).
If you use a filter to show the last 5 weeks RelativWeek=-5 then this approach will not work.If their is only one year your solution is much faster.

Kind regards Lukas

Imke Feldmann
Fr, 12.02.2016 - 01:10

Hi Lukas,
thanks for resoponding. Yes, you're right - this wouldn't survive a year change - this escaped me.
So now, with this new formula you don't need any helper column at all, just put this new column into your DimDate-Table:
(Number.From(Date.StartOfWeek([Date]))-Number.From(Date.StartOfWeek(DateTime.LocalNow())))/7
BR, Imke

Lötters
Lukas
Lötters
Fr, 12.02.2016 - 08:38

Hi Imke,
thanks for your feedback and the easier forumlar. See my post update.
Kind regards Lukas

Neuen Kommentar schreiben

Der Inhalt dieses Feldes wird nicht öffentlich zugänglich angezeigt.

Klartext

  • Keine HTML-Tags erlaubt.
  • HTML - Zeilenumbrüche und Absätze werden automatisch erzeugt.
  • Web page addresses and email addresses turn into links automatically.
Teilen auf

Newsletter Anmeldung

Abonnieren Sie unseren Newsletter!
Lassen Sie sich regelmäßig über alle Neuigkeiten rundum ORAYLIS und die BI- & Big-Data-Branche informieren.

Jetzt anmelden

Unsere Website benutzt Cookies, um bestmögliche Funktionalitäten bieten zu können. Durch die Nutzung unserer Website, erklären Sie sich mit der Verwendung von Cookies einverstanden. In unserer Datenschutzerklärung finden Sie unter §5 Informationen und Links, wie Sie diesen Maßnahmen jederzeit widersprechen können.

Datenschutzerklärung