topic Re: Selecting the effective Rate for a specific Time Entry Date in DAX Commands and Tips

Selecting the effective Rate for a specific Time Entry Date

Anonymous — Tue, 26 Jul 2022 20:55:17 GMT

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

Re: Selecting the effective Rate for a specific Time Entry Date

vapid128 — Tue, 26 Jul 2022 22:18:00 GMT

In your second table add colnum

rate =

Calculate(

max(table1[Rate]),

Filter(table1,

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

table1[date]<table2[date]
)
)

Re: Selecting the effective Rate for a specific Time Entry Date

Anonymous — Wed, 27 Jul 2022 13:04:10 GMT

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

Re: Selecting the effective Rate for a specific Time Entry Date

Anonymous — Fri, 29 Jul 2022 08:19:48 GMT

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.