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

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.