![Power-BI-Content-Article.png](https://quantinsightsnetwork.com/wp-content/uploads/2024/04/Power-BI-Content-Article-678x381.png)
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.
![Screen-Shot-2021-12-02-at-10.26.16-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.26.16-PM-1024x571.png)
![Screen-Shot-2021-12-02-at-10.26.16-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.26.16-PM-1024x571.png)
As you can see, we have two Date columns here – the Order Date and the Ship Date.
![Screen-Shot-2021-12-02-at-10.36.40-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.36.40-PM-1024x571.png)
![Screen-Shot-2021-12-02-at-10.36.40-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.36.40-PM-1024x571.png)
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.
![Screen-Shot-2021-12-02-at-10.38.38-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.38.38-PM-1024x570.png)
![Screen-Shot-2021-12-02-at-10.38.38-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.38.38-PM-1024x570.png)
The events in progress pattern means that you have to create more than one relationship between the Date table and the Sales table.
![Screen-Shot-2021-12-02-at-10.39.50-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.39.50-PM-1024x573.png)
![Screen-Shot-2021-12-02-at-10.39.50-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.39.50-PM-1024x573.png)
The first relationship is between the Date and the Order Date.
![Screen-Shot-2021-12-02-at-10.43.53-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.43.53-PM-1024x573.png)
![Screen-Shot-2021-12-02-at-10.43.53-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.43.53-PM-1024x573.png)
We also need to create a relationship between the Date column and the Ship Date column.
![Screen-Shot-2021-12-02-at-10.47.04-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.47.04-PM-1024x572.png)
![Screen-Shot-2021-12-02-at-10.47.04-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.47.04-PM-1024x572.png)
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.
![Screen-Shot-2021-12-02-at-10.50.08-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.50.08-PM-1024x572.png)
![Screen-Shot-2021-12-02-at-10.50.08-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-10.50.08-PM-1024x572.png)
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.
![Screen-Shot-2021-12-02-at-11.05.15-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.05.15-PM-1024x571.png)
![Screen-Shot-2021-12-02-at-11.05.15-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.05.15-PM-1024x571.png)
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.
![Screen-Shot-2021-12-02-at-11.10.21-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.10.21-PM-1024x572.png)
![Screen-Shot-2021-12-02-at-11.10.21-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.10.21-PM-1024x572.png)
We have to calculate Total Sales within a different context, so we’ll use CALCULATE and reference Total Sales.
![Screen-Shot-2021-12-02-at-11.46.28-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.46.28-PM-1024x569.png)
![Screen-Shot-2021-12-02-at-11.46.28-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.46.28-PM-1024x569.png)
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.
![Screen-Shot-2021-12-02-at-11.48.38-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.48.38-PM-1024x570.png)
![Screen-Shot-2021-12-02-at-11.48.38-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.48.38-PM-1024x570.png)
For the second filter, we’ll use the Ship Date this time. This should be greater than or equal to the MIN Date.
![Screen-Shot-2021-12-02-at-11.50.54-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.50.54-PM-1024x571.png)
![Screen-Shot-2021-12-02-at-11.50.54-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.50.54-PM-1024x571.png)
Once we add that to our table, you’ll see that it still won’t return the correct result just yet.
![Screen-Shot-2021-12-02-at-11.52.28-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.52.28-PM-1024x571.png)
![Screen-Shot-2021-12-02-at-11.52.28-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-02-at-11.52.28-PM-1024x571.png)
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.
![Screen-Shot-2021-12-03-at-10.20.58-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.20.58-PM-1024x563.png)
![Screen-Shot-2021-12-03-at-10.20.58-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.20.58-PM-1024x563.png)
So what we’re going to do is get the SUM of the Total Revenue to get our Total Sales.
![Screen-Shot-2021-12-03-at-9.24.02-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.24.02-PM-1024x572.png)
![Screen-Shot-2021-12-03-at-9.24.02-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.24.02-PM-1024x572.png)
Now, we’re seeing the actual Sales in Progress.
![Screen-Shot-2021-12-03-at-9.28.35-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.28.35-PM-1024x571.png)
![Screen-Shot-2021-12-03-at-9.28.35-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.28.35-PM-1024x571.png)
Let’s turn this into a chart so that we can have a visual representation of how the numbers go through time.
![Screen-Shot-2021-12-03-at-9.38.13-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.38.13-PM-1024x572.png)
![Screen-Shot-2021-12-03-at-9.38.13-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.38.13-PM-1024x572.png)
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.
![Screen-Shot-2021-12-03-at-9.41.06-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.41.06-PM-1024x570.png)
![Screen-Shot-2021-12-03-at-9.41.06-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.41.06-PM-1024x570.png)
Then, instead of computing for the Total Sales, we’re going to use the COUNTROWS function and reference the Sales Data.
![Screen-Shot-2021-12-03-at-9.46.32-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.46.32-PM-1024x571.png)
![Screen-Shot-2021-12-03-at-9.46.32-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.46.32-PM-1024x571.png)
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.
![Screen-Shot-2021-12-03-at-9.52.08-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.52.08-PM-1024x571.png)
![Screen-Shot-2021-12-03-at-9.52.08-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-9.52.08-PM-1024x571.png)
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.
![Screen-Shot-2021-12-03-at-10.05.08-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.05.08-PM-1024x573.png)
![Screen-Shot-2021-12-03-at-10.05.08-PM | Master Data Skills + AI Power bi dax measures](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.05.08-PM-1024x573.png)
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.
![Screen-Shot-2021-12-03-at-10.06.11-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.06.11-PM-1024x570.png)
![Screen-Shot-2021-12-03-at-10.06.11-PM | Master Data Skills + AI](https://blog.enterprisedna.co/wp-content/uploads/2021/12/Screen-Shot-2021-12-03-at-10.06.11-PM-1024x570.png)
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
Be the first to comment