Forum Discussion

EaglesTony's avatar
EaglesTony
Icon for Post Prodigy rankPost Prodigy
1 year ago
Solved

How do I find the correct column based off a date

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,

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      EaglesTony
      Icon for Post Prodigy rankPost Prodigy
      1 year ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

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