Calculating Weekly Sales w/DAX in Power BI

9 comments

Calculating the difference between weekly sales results in Power BI is unfortunately not that easy.

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.

weekly sales

We also have to create this Year & Week just with a little bit of logic inside a calculated column.

weekly sales

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.

weekly sales

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.

weekly sales

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.

weekly sales

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.

weekly sales

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

Download

Insert your email address and press Download for access to the files used in this article.

***** Learning Power BI? *****
FREE COURSE – Ultimate Beginners Guide To Power BI

FREE COURSE – Ultimate Beginners Guide To DAX
FREE – 60 Page DAX Reference Guide Download
FREE – Power BI Resources
Enterprise DNA Membership
Enterprise DNA Online
Enterprise DNA Events

membership banner 3

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

***** Related Course Modules *****
Time Intelligence Calculations
Advanced Data Transformations & Modeling
Mastering DAX Calculations

***** Related Support Forums *****
Getting Last Week (shift specific) Data To Display In A Table
Same Period Last Year for Fiscal Week Nbr (weeks in sales table are non standard)
Filter A Table By The Aggregation Of Its Self
For more weekly sales queries to review see here…..

Enterprise DNA Events

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

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

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

Leave a Reply

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