Selecting the effective Rate for a specific Time Entry Date

[Sergec67]

Hi,

I have two tables,  Table A with effective Rates per Employees with Start Dates and Table B with Time Entries per Employees.

I would like to calculate the Cost = [Duration in Hours] * [Rate] but use the effective Rate at Date time Entry.  I've been struggling with that one.

Thanks

[v-binbinyu-msft · Accepted solution]

Hi @Sergec67 ,

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.

[vapid128]

In your second table add colnum

rate = 

Calculate(

    max(table1[Rate]),

    Filter(table1,

        table1[Employee Number] = table2[Employee Number] &&

        table1[date]<table2[date]
    )
)

[Sergec67]

Thank you the reply.  I was working on DAX measures but this will get me started.