Forum Discussion
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
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_rateBest 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
vapid128Solution Specialist
In your second table add colnum
rate =
Calculate(
max(table1[Rate]),
Filter(table1,
table1[Employee Number] = table2[Employee Number] &&
table1[date]<table2[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_rateBest 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.