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.

Announcements

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

Power BI March 2023 Update

Find out more about the March 2023 update.

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors