Retrieving previous period values in Power BI is a common task, but retrieving previous week values requires some special attention. In this tutorial, I’ll discuss two straightforward and versatile methods for obtaining Power BI’s previous week values that can be useful additions to your toolbox – one using DAX and the other using Power Query. You can watch the full video of this tutorial at the bottom of this blog.
This tutorial came from a question that I got from an Enterprise DNA member. The member asked what was the easiest way to determine the previous week’s number. This is a common question I encounter. It seems pretty simple, but the more you dig into it, the more you realize there’s quite a lot to process.
Previous Week Values In Power BI: The Problem
If we look at the week number, we’ve got 52 weeks; multiply that by seven days (a week) and that comes to 364. So, in each year, there’s an extra day, and then for leap years, there are two extra days. These one or two extra days end up causing a lot of problems.
Let’s create a measure from our Dates table. We’ll call it MAX Week # (number). In this analysis, we’re using the Enterprise DNA Extended Dates Table; this has ISO Weeknumber, which is quite helpful. Now, let’s take the MAX of the ISO Weeknumber.
Now, let’s take a table and drop Year as well as our MAX Week #. This data set has 10 years of data, and we see that the first two years have 52 weeks. Then, we’ve got two years with 53 weeks, three years with 52 weeks, and then two more years with 53 and one with 52.
There’s not really a discernible pattern here. It’s not easy to go back and predict that one year you’re going to have 52 weeks and the other year you’re going to have 53.
The next thing you might think of if you’re familiar with DAX time intelligence functions is to use the DATEADD function, which shifts a range of dates back by a given period. It does that really well for Day, Month, Quarter, and Year, but it doesn’t include Week, so we have to find a different way of doing this.
In the Enterprise DNA Extended Dates Table that we’re using, Melissa (one of the Enterprise DNA experts) has included a function called OFFSETS. Let’s dive into this function because it is absolutely key in solving this from a DAX perspective.
Looking at the current period (for example January 20th which is the period of creating this tutorial), what we see is that offset equals zero. The way offsets work is that for the current period, the offset gets zero.
The week prior to the current week is a negative one, two weeks prior to the current week is a negative two, and so on. And then, it goes forward from the positive numbers; next week then gets a one, two weeks gets a two, etc.
Each time you open the report relative to today’s date, it calculates the offsets. This is incredibly powerful and now I’ll show you how easy it is to tackle some difficult problems like this Power BI previous week number when you’re using offsets.
Obtaining Power BI Previous Week Using DAX
Let’s create a new measure and call it Previous Wk #. We’re going to start with a variable (SelWkOffset), where we’re just going to do a SELECTEDVALUE of the WeekOffset. So, in the current row, it’s going to draw that WeekOffset and put that in our variable.
The next variable will be Result. This is the ultimate result that we want and we’re going to use CALCULATE because we’re going to be shifting context.
Then, we want the MAX ISO Weeknumber and that’s going to be subject to these filter conditions. So, we’ll remove all the filters on the Dates table, and so we’ll use ALL here. If we had a date slicer, we might want to use ALLSELECTED, but for now we don’t, so we’re just going to use ALL to remove the filter from that Dates table.
And then, we’ll say the WeekOffset is going to be equal to our SelWkOffset minus one. This is where the OFFSETS become so powerful. You can’t use Weeknumber minus one because that resets every year. But because the ALL OFFSETS are sequential, you can treat it like you would treat finding the previous year and just subtracting one for each previous year. And so this, this offset functions just the same way a consecutive number would.
We can now take and close off that filter condition, closed off CALCULATE, and just RETURN our Result.
Now, let’s look at how this measure works. In the table below, we can see that it’s doing exactly what we’d hoped. In week 52 for period one in the following year, the previous week is 52. And then, it steps back one each period, and so on.
Now, what do you do in cases when you don’t have an offset?
Let’s say you’re working with a corporate Date table that comes from your data warehouse and it doesn’t include these offsets. There’s a technique that I want to show you in Power Query. The first time I saw it was in a blog entry by Imke Feldmann.
This approach works really well for any situation in which you can order the table in ascending order, and it doesn’t have to have an offset. It doesn’t have to have even a date field. It could be an entirely different type of table. As long as it’s sortable in ascending order, you can use this technique.
So let’s reference this Dates Raw table and we’ll call this Dates. Then, we make sure to sort this in ascending order.
Next, we want to group by week. The reason for that is we’re going to be adding two index columns. When we add those index columns, we want those to be at the week granularity, not at the day granularity.
Next, we want to add those index columns. The important thing here is that the first index that we’ll add is going to be a 0-based index. And then, we’re going to add a second index that’s a 1-based index.
Then, we’re going to take this column and merge a table into itself. We’re going to merge it based on the two different index columns. That’s going to give us a shift in rows.
So, if we take this and merge the first to the 0-based index to the 1-based index, it matches 521 at 522 rows. That’s exactly what we’d expect because the second table is not going to pick up the zero index since it starts at one.
After that, we’ll just expand this out properly, just using ISO Weeknumber.
That gives us our previous week number. And now, we’ll just expand our original grouping and take out the WeekEnding field and the original ISO weeknumber so that we don’t duplicate.
If we put that in a table, you can see that it’s exactly the same results as using the DAX technique. It’s working exactly as our DAX measure did, but in this case, we didn’t need to use the offsets.
Conclusion
These are two different ways in a very versatile fashion of finding the previous week number. In the first technique, you can see the power of offsets in making, what would otherwise be a difficult calculation, quite simple. The second technique works exactly as our DAX measure did, but in this case, we didn’t need to use the offsets.
Hopefully, you find this tutorial helpful. You can watch the full video tutorial below and check out the related links for more similar content.
Cheers!
Brian
[youtube https://www.youtube.com/watch?v=MjUBI1DTDdA&w=784&h=441]
