cancel
Showing results for
Did you mean: Frequent Visitor

## Selecting the effective Rate for a specific Time Entry Date

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

1 ACCEPTED SOLUTION  Community Support

Hi @Sergec67 ,

My test table:

Table A: Table B: ``````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.

3 REPLIES 3  Community Support

Hi @Sergec67 ,

My test table:

Table A: Table B: ``````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.  Solution Specialist

rate =

Calculate(

max(table1[Rate]),

Filter(table1,

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

table1[date]<table2[date]
)
) Frequent Visitor

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