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 previous week values that can be useful additions to your toolbox, one using DAX and another one 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 was from an Enterprise DNA member. The member asked what is the easiest way to determine the previous weak number. This is a common question I encounter. On the face of it, it seems pretty simple, but the more you dig into it, the more you realize there’s a fair amount there to process.
Previous Week Values In Power BI: The Problem
If we look at week number, we’ve got 52 weeks, multiply that with seven days (a week), and that’s 364. So, in each year, there’s an extra day, and then for leap years, there are two extra days. Those one or two extra days end up causing a lot of problems.
Let’s create a measure from our Dates table, and we’ll call it MAX Week # (number). In this analysis, we’re using the Enterprise DNA Extended Dates Table, and this has this ISO Weeknumber, which is quite helpful. And so, 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 say, predictably in this year, you’re going to have 52 weeks, and this 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, 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 forth. And then, it goes forward from the positive numbers, which is 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 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. So, let’s dive into it.
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’s stepping back one each period, and so on.
Now, what do you do in cases 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 on itself. We’re going to merge it on itself 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 out our original grouping, and we just 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.
These are two different ways in a very versatile fashion of finding 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.
***** Related Support Forum Posts *****
Measure Previous Week
Calculate Previous/ Last Week Sales with a Custom Calendar
Calculate Current and Previous Week
For more previous week support queries to review see here…..