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 😀

[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