Calculating the difference between weekly sales results in Power BI is unfortunately not that easy. You may watch the full video of this tutorial at the bottom of this blog.
The reason behind this is that Power BI doesn’t have a weekly-based built-in time intelligence function. The only available options are for the day, month, quarter, and year.
I’ll show you with the DATEADD function in this formula. This function is one of my favorites, as it makes time comparison so easy. However, it doesn’t give us an option to calculate for weekly sales like in this tutorial example.
Therefore, to be able to work out weekly results in Power BI, we need to utilize different logic within a formula. It’s unfortunately slightly more advanced, but it’s an important and really high-quality technique that you need to understand and utilize in your models.
If you can understand some of the formula techniques that I work through to actually calculate this result, you’re doing very well with DAX inside of Power BI. Invariably you will be able to create some really good insights and high quality analysis in your Power BI reports.
In this tutorial, I run through how to use the CALCULATE and FILTER functions, including how to utilize variables really effectively within your DAX formulas.
It’s a really great concept to understand as it will provide you with good insights, especially if you’re working with information over a series of weeks. Maybe you’re a retailer and you want to analyze this week versus last week, or this week versus the same week from the year before.
This tutorial shows you how such great analysis can be done if you utilize these formula techniques in combination with your data models.
The Data Set Up
First of all, we have to have a Week Number inside our Date table. We’re not going to be able to do these calculations unless we have it here.
We also have to create this Year & Week just with a little bit of logic inside a calculated column.
When we have that context, we can layer it into a table. In this case, we start with Total Sales, which is a pretty simple calculation that I use over and over in my tutorials.
Now I’ll show you the first way I tried to make a calculation to get the previous weeks sales and why it didn’t work.
Problem With The Initial Calculation
The calculation is Previous Week Sales and I use CALCULATE Total Sales. On the next line, I put FILTER.
The FILTER function enables us to put this logic inside it. It would then iterate through the table that we specify and check whether what we’re iterating through is true or false. If it’s true, it leaves it inside the context of the Total Sales calculation, and it’s what we ultimately do inside CALCULATE.
So we put FILTER ALL Dates and then write the logic in here. We want to calculate this in the week before, and the SELECTEDVALUE function is great for this type of calculation. It’s going to jump back to one week before and will do the same with our year.
If we bring that in, you’ll see that it doesn’t work effectively.
There’s no result in the first week of the data, which is 2015, and that’s okay since it’s the beginning of the data. But if we get down to the first week of 2016, we get nothing.
This is because based on our formula, it doesn’t know what to do when it gets to 1. So 1 minus 1 is 0 and that’s where it goes wrong.
Now I’ll go through the solution that I did that actually works and hopefully you can see how you can use similar logic just in a slightly different way.
Solution To Get Weekly Sales Calculation
In our calculation, we’ll use a lot of variables. This is how I recommend setting things out that become a little bit more complicated with a little bit more logic.
So we’ll do a new measure and call it Sales PW (previous week). Then, we’re going to add in a few variables. SELECTEDVALUE is going to evaluate every single calculation of the current week, which is very similar to what we were doing inside our other formula.
For our MaxWeekNumber, we use the CALCULATE function. We want to calculate the maximum of all weeks that are possible. So in theory, we want to return 53 every single time. Furthermore, we use the SUMX and FILTER functions in our logic.
There’s quite a bit going on in there, but this is seriously what you can get inside of DAX. And now when we drag it in, we see how it solves the problem.
We then make this into a visual to clearly see the difference in the total sales from the previous week.
***** Related Links *****
Compare Time Periods With Non Standard Date Tables – DAX & Power BI
Placing Workday And Weekend Day Numbers Into The Date Table In Power BI
Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX
Conclusion
This tutorial demonstrates how you can ultimately calculate the difference between weekly sales results with DAX in Power BI.
In the example, I focused on 53 weeks. There is a potential issue if there’s only 52 weeks, but we need to find the solution for that separately in another tutorial.
I hope those of you who are dealing with custom calendars can understand a little bit about how you can solve some of the things that you may be looking at.
Cheers!
Sam
