Finding overlapping dates and changing them to the new Earliest and Latest range.

[JCatNR]

Hello!

I have a data table that has Order ID, Customer ID, Type, Valid(T/F), Start Date, and End Date. There is already a DAX formula I have created that checks if a customer has an order where the start and end date of one of the order overlaps with another order for the same customer, it populates the DAX column to the earliest start date and the latest end date. If an order has no overlap, the start and end date stay the same.

This works fine for basic overlapping orders or orders that are totally within another order, but an issue arises if there are 3 or more overlapping orders that are spread out. Here is an example of the issue:

Order ID	Customer ID	Type	Valid(T/F)	Start Date	End Date	Min Start Date	Max End Date	Correct Start Date	Correct End Date
1	1	2	T	1/1/2025	1/4/2025	1/1/2025	1/7/2025	1/1/2025	1/9/2025
2	1	2	T	1/4/2025	1/7/2025	1/1/2025	1/9/2025	1/1/2025	1/9/2025
3	1	2	T	1/6/2025	1/9/2025	1/4/2025	1/9/2025	1/1/2025	1/9/2025
4	2	6	T	1/1/2025	1/8/2025	1/1/2025	1/8/2025	1/1/2025	1/8/2025
5	2	6	T	1/5/2025	1/7/2025	1/1/2025	1/8/2025	1/1/2025	1/8/2025
6	2	6	T	1/15/2025	1/21/2025	1/15/2025	1/21/2025	1/15/2025	1/21/2025

So as this shows, my current DAX formula works fine for Order 2 since 1 and 3 overlaps it. However, due to Order 3’s start date being after Order 1’s end date, it thinks it’s totally separate.

What I think is needed is a loop that goes through to check if the New Start Date and New End Date changes, the loop goes back through all orders (which would fix the above example after two loops). The occurances of orders overlapping each other could theoretically be infinite, which is why a loop is needed instead of just making a New Start Date2 or something like that.

That being said, I am totally open to other suggestions. After doing some research, it seemed like this may be easier to use a Function in Power Query rather than DAX. But with that comes the next problem of how to populate the New Start/End Dates in Power Query based on the other dates.

[v-saisrao-msft]

Hi @JCatNR,

We haven’t heard back from you regarding your issue. If it has been resolved, please mark the helpful response as the solution and give a ‘Kudos’ to assist others. If you still need support, let us know.

Thank you

[v-saisrao-msft]

Hi @JCatNR,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-saisrao-msft]

Hi @JCatNR,

I wanted to check if you had the opportunity to review the information provided by @lbendlin . Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[lbendlin]

No need for any of this.  Use CALENDAR and INTERSECT - that will tell you the number of days two orders overlap by. You can do that across all orders for a customer - with the minimal start date and the maximal end date as one of the CALENDARs and the concatenation of the order intervals as the other (via GENERATE) you can then identify the gaps too.

[JCatNR]

Thank you for your reply!

I think that makes sense in theory: create a calendar that has the earliest start date and latest end date for a customer, and compare all those dates to the start and end dates of that customer's orders. Returned dates from the intersect show the days included in all start and end dates. Right?

If that is correct, should I be creating this DAX function in the Order table? One for the MIN/Start date value, and another for the MAX/End date? I guess I am a little confused about how/where I should be doing all these calculations.

[lbendlin · Accepted solution]