It’s important for many businesses to make sure that you are analyzing over the correct sales periods or correct time periods in which you actually have results. You may watch the full video of this tutorial at the bottom of this blog.
If you implement formulas incorrectly in Power BI, especially with iterating functions like AVERAGEX or SUMX, and you iterate over days when you don’t have results, or days without any sales, then you are actually going to get incorrect results.
Sometimes, unknowingly, you’ll place these results into your reports and not immediately pick up on whether they are correct or incorrect.
In this tutorial, I’m going to demonstrate how you can isolate weekdays and weekends and only retrieve information for those particular time periods.
This is going to be particularly valuable if you’re an organization that only sells during the week or if you want to isolate your weekend sales, as this might be where the bulk of your sales is happening.
Working Out DAX With The Data Model
The main thing is to work out DAX with the data model effectively. First, let’s look at what I’ve done in the Dates table.
Obviously, we need to have DAYINWEEK. When we don’t have this in our Dates table, we probably try and get it in there with a calculated column. But ultimately, we want to make the DAX as simple as possible at the back end. So, it’s better to sort this out in the data model.
We obviously have this DAYINWEEK aligned to a Day of the Week.
From there, we can actually derive whether it is a weekend or a weekday. I’ve utilized this SWITCH formula to achieve that.
This is working through all the logic inside of the DAYINWEEK column and returning as a weekday or a weekend.
Now let’s run through how to write the formulas used in analysing sales periods.
Writing The Formulas
We use the CALCULATE function for our calculations. For Sales Weekdays, we go CALCULATE Total Sales, and then we FILTER through our Dates table everything for the Weekday.
The Weekend – Weekday is the column we just created (with the SWITCH formula).
Then, we format our formula
and bring it in, and we’ll see that we get different results, showing the weekdays.
And then from here, we just copy and paste this formula and simply change Weekends to Weekends.
We then drag it in, and we can see the results. The sales of weekdays and sales of weekends are going to give us the Total Sales.
So you can see here how we use the FILTER function really effectively. We use it in combination with the data model. We added this dimension or this column based on some logic inside of that table, and then we were able to put the FILTER function on top of it.
Re-Using The Formula And Branching Out
Moreover, we could use this formula in any way, shape, or form. For example, we want to work out Saturday sales.
To get our Sales Saturday, all we have to do is change the logic inside. Instead of Weekend – Weekday, we go Day of Week.
Format it and drag it in.
Once we get here, we could branch out into a few other things using our measures, such as how many Saturday sales we have. So to demonstrate that, let’s try to work it out.
We call it % (Percent) of Sales on Weekend. We DIVIDE Sales on Weekends by our Total Sales.
We can do a percentage with this, and then drag it down to our table like this.
So that’s one way to isolate certain elements in your data to analyse over sales periods correctly.
From here, we can branch out even more and find really great insights across various different factors in our data model or even compare one to the other. You can even compare weekday results to weekend results.
***** Related Links *****
Calculating Average in Power BI: Isolating Weekday or Weekend Results Using DAX
Calculate Consecutive Weekday & Weekend Days In Power BI Using DAX
Placing Workday And Weekend Day Numbers Into The Date Table In Power BI
Conclusion
In this tutorial, we’ve gone through a few steps to actually get to the correct sales periods evaluation. Furthermore, we branched out even more from there.
The key concept to understand here is iterating functions. You must get your head around these and understand how they work very well. If you do, you will see that you can change the shape of any virtual table within these functions to be able to isolate, in this case, any time period.
There are plenty of great analytical insights that you can discover by implementing these ideas and techniques well.
All the best,
Sam
[youtube https://www.youtube.com/watch?v=2umfdZElvDU?rel=0&w=784&h=441]
