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
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?