A more complex "is date between 2 dates" issue

Question

Hi All,

I know this will probably seem like it's been covered, but I haven't come across a similar scenario.

I have a series of projects along with the start and end dates for the projects. Now, because this is going out to Tableau I had to build  a Date Scaffold because I need to know whether or not the project is active in any given week. Because of this, Every project has a record for every week interval bothe before and after the project start/end dates.

I've done plenty of If date between date range calcs, but the usual IF AND calcs don't work for this.

Using the attached sample, I I'm trying to determine if a project code is active during each week interval. It gets trickier because the project end date will be outside the week interval; so you can't just do the usual If Date A is greater than Date B and Less than Date C logic since there are 4 dates to take into  account(Project Start Date, Project End Date, Week Start Date, Week End Date).

Thanks for any help!

Craig

Thanks for any help

Sample.csv

TomWelgemoed · Answer

Hi @csh8428 ,

Maybe something like the attached will work for you. When you're question is actually at Date level, I like to generate each of the dates between weeks and directly match on the date to see a project starts/finishes.

Here I've used the "Generate Rows" tool to generate all the dates between the Week Start & Week End as well as between the Project Start & End to then see where they overlap. Note: The weeks did not cover all the project dates, so you have some project dates that don't fall in a week in this example. You can simply just extend your week "Scaffold" to cover these weeks and then use the same workflow.

Let me know if this is helpful/on the right track?

Calendar.yxmd

Qiu · Answer

@csh8428

Can you provide a sample of output?