In this tutorial, I’m going to show you how to create Power BI DAX measures for events in progress. These events could come in the form of orders in progress or sales in progress, depending on the nature of your report.
Basically, the challenge here is calculating results when there is more than one date within the fact table. The presence of similar elements could cause errors in your results, so let’s see how we can go past that.
Dealing With Two Relationships Between The Same Tables
This is how our Sales table looks like.
As you can see, we have two Date columns here – the Order Date and the Ship Date.
The fact that there are two date tables here can bring in even better analysis. How many orders do we currently have between the Order Date and Ship Date? How many orders are currently in progress?
This type of analysis doesn’t necessarily have to be applied to this specific scenario, too. There’s a world of applications knowing that it’s going to be the same pattern over and over again. Once we start working on the DAX formula, you’ll see what that pattern is.
But before jumping into the formula, there are a few things to look into especially in terms of relationships between the tables. So let’s take a look at our data model to better understand what’s going on.
The events in progress pattern means that you have to create more than one relationship between the Date table and the Sales table.
The first relationship is between the Date and the Order Date.
We also need to create a relationship between the Date column and the Ship Date column.
But you also have to make these two relationships inactive because you can’t have one relationship overriding another. Otherwise, you’ll encounter issues on your report.
Here’s an example. If we drag the Total Sales measure into the report, you’ll see that we’re getting odd results. It’s showing the Total Sales all throughout the dates.
The reason it’s showing up like that is that it doesn’t know what to filter. That’s because the two relationships in the background are cancelling each other out. For any events in progress like this to run properly, we’re going to have to decide between two options.
The first option is to make a separate table solely for this calculation. The second one would be to create a CALCULATE statement that uses the USERELATIONSHIP function to compute for the core measures.
Power BI DAX Measures For Events In Progress
Let’s work on the second option, which is to create a measure for events in progress applicable to this scenario.
We’ll start off with a CALCULATE statement that uses the SUM for our Total Revenue. Then, we’ll use the USERELATIONSHIP function and reference the Order Date column in the Sales Data table.
This marks the pattern that we’re going to use in our formula for any event in progress. Again, this means that there would be no need for us to recreate another Date table in our model.
Let’s try it for Sales in Progress.
We have to calculate Total Sales within a different context, so we’ll use CALCULATE and reference Total Sales.
Next, we need to create two filters that will do the analysis for us. So we’ll start off with the FILTER function, then use VALUES for the Order Date. Then we’ll add that the Order Date is less than or equal to the MAX Date.
For the second filter, we’ll use the Ship Date this time. This should be greater than or equal to the MIN Date.
Once we add that to our table, you’ll see that it still won’t return the correct result just yet.
That’s because even if we have Total Sales as a key measure, we haven’t calculated that yet. Our model doesn’t have Total Sales, it only has Total Revenue.
So what we’re going to do is get the SUM of the Total Revenue to get our Total Sales.
Now, we’re seeing the actual Sales in Progress.
Let’s turn this into a chart so that we can have a visual representation of how the numbers go through time.
Reusing Power BI DAX Measures
See how straightforward it is to reuse this measure. Earlier, we were looking at the Sales in Progress. Technically, the fact that it is still in progress means that there is no revenue just yet; you will only get paid once the orders have actually been sent out.
This presents another great piece of analysis. What sort of cashflow will you need to make the products before you can send an invoice and receive payment? To find out, let’s calculate the Orders in Progress. We just need to change the name of the measure here.
Then, instead of computing for the Total Sales, we’re going to use the COUNTROWS function and reference the Sales Data.
Basically, this new measure is just calculating how many orders are being processed. I’m not going to bring it in as a column in our table, but it would be good to have that visualization in our report.
So let’s copy and paste the chart for the Sales in Progress, then change the values into Orders in Progress.
Now, we can see how many actual orders are in progress. This helps us understand how many products have been ordered on any particular day, but have not been shipped out yet.
Creating Additional Filters
Aside from what we’ve already seen, we can get additional insights by adding filters to our report. One good filter to have would be the Product Name. This will allow us to see trends in terms of how many orders are made for specific products.
So what if I wanted to see how many people ordered Product 6 and how much revenue we’re expecting from it? It’s easy as ticking that off the filter.
There are also other filters that would be great to have depending on what kind of analysis you’re looking for. You can have a filter for customers, for instance. This will help you determine who your biggest customers are. You could also add a filter for regions to help determine where these orders are coming from.
***** Related Links *****
DAX Measure Analysis: Breaking Down Long DAX Measures
DAX Measures In Power BI Using Measure Branching
Implementing DAX Measure Groups Into Your Reports – A Power BI Modeling Review
Conclusion
There is a lot of logic running behind the scenes when we use Power BI DAX measures like this. Just the fact that the measure is evaluating every single row in the Sales Table while also making sure that it’s meeting the right conditions for the Order Date and the Sales Date is quite impressive.
That’s why I recommend learning the pattern and reusing it multiple times. This allows you to become more familiar with the pattern, making it easier in the long run. Make sure to add filters as well so that you can really slice up the analysis in different ways and bring even more great insights to life.
All the best,
Sam
