Calculating if date range has any dates within another date range (overlap)

[ohhStudiio]

Hello!

I am working on building a gantt chart for operational maintenance and we need to determine the if the start date and end date of a work order are within a 1 to 6 week lookahead window. The work week always starts Monday to Sunday. Our 1st date in the weeks 1 to 6 range is always the next upcoming monday. If today is Thursday, May 23rd, our week 1 date would be Monday, May 27th. Our week 6 date would be the week 1 date plus 42 days.

I was trying to add a calculated column to get the week 1 date so I could compare the Start date and Week 1 date against eachother.

Column = CALCULATE(DATE(YEAR(TODAY()),1,-2),-WEEKDAY(DATE(YEAR(TODAY()),1,3))+WEEKNUM(TODAY()+1)*7)

But I get an error "The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

At the end of the day I am open to all solutions even if it means changing how I am going about it, I have 3 columns, Work Order #, Start Date, End Date. I need to be able to identify the following 4 criteria to be able to filter to only rows that meet any of them:

- Start Date <= Week 1 Date && End Date>= Week 1 Date

- Start Date >= Week 1 Date && End Date<=Week 6 Date

- Start Date >= Week 1 Date && End Date>= Week 6 Date

- Start Date<= Week 1 Date && End Date>= Week 6 Date

Any support would be greatly appreciated!

[K0da · Accepted solution]

Hi,

i assume your Dataset is something like this:

im using day/month/year format for the date.

so i've made three columns, Week 1, and Criteria:

Week_1 = 'Dataset'[Work_Order_Start_Date] - WEEKDAY('Dataset'[Work_Order_Start_Date],2)+8

Week_6 = 'Dataset'[Week_1]+42

Criteria = 
/*
- Start Date <= Week 1 Date && End Date>= Week 1 Date
- Start Date >= Week 1 Date && End Date<=Week 6 Date
- Start Date >= Week 1 Date && End Date>= Week 6 Date
- Start Date<= Week 1 Date && End Date>= Week 6 Date
*/
SWITCH(
    TRUE(),
    'Dataset'[Work_Order_Start_Date]<='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] >='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]<='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] >='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] <='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes"
)

the result:

hope that can help 😀

[Deevo_]

Hi @K0da @ohhStudiio 

Thanks so much for this post. The solution is very neat and works for me.

I did notice the 1st row from the "Criteria" and the SWITCH statement rules are different.

My tests on my own dataset concludes that the Criteria rules are correct and not the SWITCH statement.

- Start Date <= Week 1 Date && End Date>= Week 1 Date
- Start Date >= Week 1 Date && End Date<=Week 6 Date
- Start Date >= Week 1 Date && End Date>= Week 6 Date
- Start Date<= Week 1 Date && End Date>= Week 6 Date

[K0da · Accepted solution]

Hi,

i assume your Dataset is something like this:

im using day/month/year format for the date.

so i've made three columns, Week 1, and Criteria:

Week_1 = 'Dataset'[Work_Order_Start_Date] - WEEKDAY('Dataset'[Work_Order_Start_Date],2)+8

Week_6 = 'Dataset'[Week_1]+42

Criteria = 
/*
- Start Date <= Week 1 Date && End Date>= Week 1 Date
- Start Date >= Week 1 Date && End Date<=Week 6 Date
- Start Date >= Week 1 Date && End Date>= Week 6 Date
- Start Date<= Week 1 Date && End Date>= Week 6 Date
*/
SWITCH(
    TRUE(),
    'Dataset'[Work_Order_Start_Date]<='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] >='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]<='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] >='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes",
    'Dataset'[Work_Order_Start_Date] <='Dataset'[Week_1] && 'Dataset'[Work_Order_End_Date]>='Dataset'[Week_6],"yes"
)

the result:

hope that can help 😀

[ohhStudiio]

Hello!

Thank you so much for the response as this was my first time utilizing the forum for help and I honestly wasnt expecting much at all. Thank you for making this a great experiance!

That code worked amazing for the criteria requirements, and week 6.  The only thing that needs to be adjusted is how Week 1 is calculated.

Week 1 is always the upcoming monday from todays date. For example with today being May 23rd, Week 1 would be from May 27th to June 2nd (Mon-Sun). Once it becomes May 27th, the new week one would be from June 3rd to June 9th, and so on. 

In excel my formula to do this is:

=(DATE(YEAR(TODAY()),1,-2)-WEEKDAY(DATE(YEAR(TODAY()),1,3))+(WEEKNUM(TODAY())+1)*7)

Im not quite sure how I came up with that, but it always generates the upcoming monday based on todays date and year. I just cant figure out how to get it to work in Power BI. If this can figured out then we have an amazing solution!

[K0da · Accepted solution]

Hello,

"Week 1 is always the upcoming monday from todays date", if that is the case you can change the Week 1 column to:

Week_1 = TODAY() - WEEKDAY(TODAY(),2)+8

That way week 1 and week 6 will always change accordingly to todays date