Forum Discussion

ohhStudiio's avatar
ohhStudiio
New Member
2 years ago
Solved

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

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...
Need Help
  • K0da's avatar
    K0da
    2 years ago

    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's avatar
    K0da
    2 years ago

    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

K0da's avatar
K0da
New Member
2 years ago

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's avatar
ohhStudiio
New Member
2 years ago

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's avatar
    K0da
    New Member
    2 years ago

    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