# How To Compare Two Lists Of Calculated Data Virtually – An Advanced DAX Technique

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.

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

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