Selecting the effective Rate for a specific Time Entry Date

Hi Anonymous ,

My test table:

Table A:

Table B:

Please try below measure:

Cost =
VAR cur_duration =
    SELECTEDVALUE ( 'Table B'[Duration in Hours] )
VAR cur_datetime =
    SELECTEDVALUE ( 'Table B'[Date time entry] )
VAR cur_num =
    SELECTEDVALUE ( 'Table B'[Employee Number] )
VAR cur_rate =
    CALCULATE (
        MAX ( 'Table A'[Rate] ),
        FILTER (
            'Table A',
            'Table A'[Employee number] = cur_num
                && 'Table A'[Effective Start Date] < cur_datetime
        )
    )
RETURN
    cur_duration * cur_rate

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.