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

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

Debugging DAX: Tips and Tools for Troubleshooting Your Formulas

One of the main reasons why businesses all over the world have fallen in love with Power BI is because...

Practical Application of TREATAS Function in DAX

A hands-on project focused on using the TREATAS function to manipulate and analyze data in DAX.

MAXX in Power BI – A Detailed Guide

A hands-on guide to implementing data analysis projects using DAX, focused on the MAXX function and its combinations with other essential DAX functions.

Leveraging the COUNTX Function In Power BI

Learn how to leverage the COUNTX function in DAX for in-depth data analysis. This guide provides step-by-step instructions and practical examples.

Using the FILTER Function in DAX – A Detailed Guide With Examples

A comprehensive guide to understanding and implementing the FILTER function in DAX, complete with examples and combinations with other functions.

DATESINPERIOD Function in DAX – A Detailed Guide

Learn how to implement and utilize DAX functions effectively, with a focus on the DATESINPERIOD function.

Using the DISTINCT Function Effectively in DAX

A systematic exploration of the DAX DISTINCT function to optimize data analytics.

Guide and Many Examples – ALL Function in DAX

A detailed guide to understanding, implementing, and mastering the DAX ALL function, complemented by practical examples and combinatory techniques.

Detailed Guide to SWITCH function in DAX

A comprehensive guide to mastering DAX functions in Power BI for conducting advanced data analysis.

SUMMARIZE Function in DAX – A Deep Dive

A comprehensive guide to using the DAX function SUMMARIZE in Power BI, with detailed explanations and practical examples.

Your Data Visualization Doesnâ€™t Look Great. What Should You Do?

Data visualization is the key to unlocking the insights hidden within your data. But, what if your...

Leveraging Power BI for Data-Driven Decisions

In the world of data analytics, thereâ€™s a constant demand for tools that not only help you make sense...