How do I find the correct column based off a date

[EaglesTony]

I have the following table:

Based off this I have another table(i.e. table2):

Key    StartDate    EndDate

ABC    1/3/2024   4/1/2024

DEF     2/1/2024   4/1/2024

HIJ      4/4/2025   7/1/2024

I need to get the value from the first table where the StartDate falls into it and EndDate, so I would end up on table2:

Key    StartDate    EndDate         PIBasedOnStartDate         PIBasedOnEndDate

ABC    1/3/2024   4/1/2024        2024-PI1                           2024-PI2

DEF     2/1/2024   4/1/2024       2024-PI1                            2024-PI2

HIJ      4/4/2025    7/1/2024      2025-PI2                            2025-PI2

I need this to get a final table as 

Key    PIFallsIn

ABC   2024-PI1

ABC   2024-PI2

DEF   2024-PI1

DEF   2024-PI2

HIJ     2025-PI2

Thanks,

[Ashish_Mathur]

Hi,

Cannot undestand how you generated the last 2 columns as seen in the second table.

[Anonymous · Accepted solution]

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.

[EaglesTony]

It looks like the 2 calculated column syntax look the same ????

[Anonymous]

Hi @EaglesTony 

Sorry for the late reply, it was my fault that I accidentally copied the same calculated column.

It should be these 2 calculated columns:

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] )

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

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.

[parry2k]

@EaglesTony your post doesn't explain what is the logic to calculate PI values?

[EaglesTony]

I actually did this in DAX, but thanks.