# Calculating Weekly Sales w/DAX In Power BI

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 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.

## 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

***** Related Course Modules *****
Time Intelligence Calculations
Mastering DAX Calculations

## 14 comments on “Calculating Weekly Sales w/DAX In Power BI”

1. Hi Sam, your solution works perfectly for a summarized view, but what would you suggest in case I want to bring in more details to the table, ie. Performance Country,Product Model, Product Family that way the Previous Week measure is not usable as it will always give a number total.

1. Shouldn’t be an issue if your data model has the correct relationships. The natural context should filter these correctly with the same formula

2. Oksana K. says:

Hi Sam, I just wanted to thank you for this tutorial. I could not find any better solution for my problem. I needed to find Week over Week Volume delta and I used your approach to calculate the difference in volume from current week to previous week. I’m looking only at one year at a time and get a correct weekly trend, so this approach works. Not sure if it would work if I needed to look at multiple years. In this case, many thanks!

1. That’s great. It should work for multiple years as that’s how it has been setup up.

3. JoJo says:

Hi Sam , thank you for this method but i have an issue. My issue is that my date table is : 15th of every month and last date of every month. The last date can be 28 or 29 in case of february and 30th or 31st depending on the month. Because of that the difference between my week numbers ,though most times is 2, can be 3 at sometimes. How do you suggest i adapt this formula to my particular case ?

4. Pete Jones says:

Hi Sam, great solution which I’ve got to work with a report I was writing. Thank you. What would I have to do to get totals working? My report is reporting a week on week sales variance by product and I need to show a weekly total as well.

5. Arul says:

What if there are multiple entries for the same year and week ? Say I’ve got 10 regions Paris, London, Tokyo, Munich so on and for city I will have the data for 2020 1st week. In that case sumx function aggregates all 2020 1st week data. How do I restrict that ? Will Groupby function help ?

1. Hi, thanks for your interest in EDNA Blog.

Response to your query based on description and sample provided will depend on the actual requirement.

To show “PY” values for different cities in visual, only addition will be to add Cities in the visual. This will add a new Evaluation Context for Cities and DAX will return values for selected city only on each row.

For other, like doing any Aggregate based on Cities without adding cities on visual, needs to use nested Iterator like SUMX, AVERAGEX inside given “PY” Query

6. Bren1990 says:

Hi Sam,

This was a great tutorial thank you. Is there a resource available on how to solve years with 52 weeks?

Thanks!
Brendan

1. Hello Bren1990,

Thank You for posting your query onto our blog post.

We’re providing a link below of a video from where you can also download the resources used into the video.

For furthermore queries, you can also reach out to us onto our Community Forum where our members as well as experts team will be able to assist you in a better and efficient manner. Below is the link of the forum provided for the reference.

Hoping you find this useful. Happy Learning!!!

This site uses Akismet to reduce spam. Learn how your comment data is processed.