How do I find the correct column based off a date

Hi EaglesTony 

Please try this:

First of all, add 2 calculated columns in the Table 2:

PIBasedOnStartDate =
VAR _vtable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS ( 'Table 2', "_StartDate", 'Table 2'[StartDate] ),
            'Table'
        ),
        [_StartDate] >= 'Table'[StartDate]
            && [_StartDate] <= 'Table'[EndDate]
    )
RETURN
    MAXX ( FILTER ( _vtable, [_StartDate] = 'Table 2'[StartDate] ), [PI] )

PIBasedOnStartDate =
VAR _vtable =
    FILTER (
        CROSSJOIN (
            SELECTCOLUMNS ( 'Table 2', "_StartDate", 'Table 2'[StartDate] ),
            'Table'
        ),
        [_StartDate] >= 'Table'[StartDate]
            && [_StartDate] <= 'Table'[EndDate]
    )
RETURN
    MAXX ( FILTER ( _vtable, [_StartDate] = 'Table 2'[StartDate] ), [PI] )

The result:

Then add a Calculated table:

Outcome =
SUMMARIZE (
    UNION (
        SELECTCOLUMNS (
            'Table 2',
            "Key", 'Table 2'[Key],
            "PIFallsIn", 'Table 2'[PIBasedOnStartDate]
        ),
        SELECTCOLUMNS (
            'Table 2',
            "Key", 'Table 2'[Key],
            "PIFallsIn", 'Table 2'[PIBasedOnEndDate]
        )
    ),
    [Key],
    [PIFallsIn]
)

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.