Forum Discussion

UBComma's avatar
UBComma
Icon for Helper III rankHelper III
5 years ago
Solved

Identifying overlaps in date sequences

I need to find conflicts in date ranges that overlap.  In this case I have many "Projects" and many "Resources".  A given Project will need a Resource for a stated date range.  The date ranges will s...
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    I'm imagining you want a calculated table like this:

     

     

     

    Here's the code to generate:

    CalcTable =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR (
                MIN ( 'Resource Allocations'[Start Date] ),
                MAX ( 'Resource Allocations'[End Date] )
            ),
            VALUES ( 'Resource Allocations'[Resource] )
        ),
        "Count",
            COUNTROWS (
                FILTER (
                    'Resource Allocations',
                    'Resource Allocations'[Resource] = EARLIER ( [Resource] )
                        && 'Resource Allocations'[Start Date] <= EARLIER ( [Date] )
                        && 'Resource Allocations'[End Date] >= EARLIER ( [Date] )
                )
            )
    ),
    [Count] > 1
)

     

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

That sounds about how I'd try to do it (the counting per day). To generate the days, the simplest approach is probably is something like CALENDAR ( MIN ( Table1[Start Date] ), MAX ( Table1[End Date] ) ).

  • UBComma's avatar
    UBComma
    Icon for Helper III rankHelper III
    5 years ago

    That will produce the date series but I'm struggling with how to generate that in a table that sequences [Project], [Resource], [Day in use] from the "Resource Allocations' table that has the Start and End dates.

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      I'm imagining you want a calculated table like this:

       

       

       

      Here's the code to generate:

      CalcTable =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR (
                MIN ( 'Resource Allocations'[Start Date] ),
                MAX ( 'Resource Allocations'[End Date] )
            ),
            VALUES ( 'Resource Allocations'[Resource] )
        ),
        "Count",
            COUNTROWS (
                FILTER (
                    'Resource Allocations',
                    'Resource Allocations'[Resource] = EARLIER ( [Resource] )
                        && 'Resource Allocations'[Start Date] <= EARLIER ( [Date] )
                        && 'Resource Allocations'[End Date] >= EARLIER ( [Date] )
                )
            )
    ),
    [Count] > 1
)

       

      • UBComma's avatar
        UBComma
        Icon for Helper III rankHelper III
        5 years ago

        I think that's a brilliant solution.  I have it working in my model and I learned a lot!  Thank you.