When working with calculated data, comparing different data sets will sometimes be necessary. I’m going to show you how to do that by combining different table functions. You may watch the full video of this tutorial at the bottom of this blog.
The INTERSECT function is the best thing to use in cases like these. Some may also say that EXCEPT is just as useful in comparing calculated data. But once you figure out how to use INTERSECT then it follows what you’ll be able to apply to the EXCEPT function in your calculations as well.
Sample Data
To show what INTERSECT can do, I’m going to show you how to compare customers from two different time periods.
In the example below, I have a date slicer to help me zone in on the time frame I need. I also have a column for the states where customers place their orders. Of course, this could be a completely different dimension depending on the insights that you’re trying to get.
I also have my Total Revenue for each state, which is also shown in the visualization.
Now I want to see how many of the customers who made a purchase at a specific date last year also made a purchase on the same date this year. That means I’m looking at two specific lists of customers who made a purchase on the same date but in different years. From there, I’m going to find out how many customers appear on both lists.
Comparing Customers From Two Time Periods
Before I can compare those two customer lists, I have to come up with the Total Customers first.
The Total Customers include those who made purchases at different stores in different states within the specified time frame. I used a simple formula to come up with this result.
I used the VALUES function because it brings up a single list of unique values. In this case, it’s going to show us the customers who bought products at the specified time frame.
I also used the COUNTROWS function. This will count the number of unique values from the list.
I’m referencing the Sales table for this measure.
Why not use the Customer table instead?
If I reference the Customer table directly, I’ll only end up counting every single customer regardless of the time frame. I need to make sure that the right context is used. By using the Sales table, I don’t only get the customers, I also get information where and when they made their purchase.
Now that I have the Total Customers, I can come up with the list of customers from the same date, but last year. This is where the measure becomes a bit more complicated.
Let me go into each part of this measure bit by bit.
Remember that I’m trying to change the context of the table. This is why I’m going to use the CALCULATETABLE function.
CALCULATE is a familiar function because I use it in my examples most of the time. It changes the context of a specific calculation. CALCULATETABLE, on the other hand, changes the context of an entire table.
Since I want to change the time frame, I’m going to use the DATESBETWEEN function.
Using CALCULATETABLE allows me to add filter parameters. By using the DATESBETWEEN function, I can zone in on a time frame that falls 365 days below the MIN and the MAX dates. In short, it pushes the time frame back to last year and creates another list of customers from there.
Again, I’m using the COUNTROWS function to calculate the number of customers after the additional filters have been applied.
Now that I have two lists, I can bring those measures I made into the INTERSECT function.
The INTERSECT function will virtually look into the list being referenced. In this case, that’s the Customer Name Index. It will go through every one of those customers and compare them with the second list from one year ago.
As it compares the calculated data, it looks at the ones that appear in both lists. For example, the table below shows that 224 customers made purchases on the same date on two different years in California.
***** Related Links *****
How to Maximize The Use of INTERSECT Function – Advanced DAX
Create Reports In Power BI To Show Deviations And Insights
New Customer Analysis Using Advanced DAX
Conclusion
Hopefully, you can now imagine other scenarios where the INTERSECT function can be used to compare two sets of calculated data.
You can even present the comparison in percentages and apply some measure branching techniques. You can look at the customers who did not come back and send out some marketing material to them.
This really shows how much insight you can get once you get the hang of using different DAX functions in Power BI.
All the best,
Sam
