Measure that returns values bases upon effective date

Thank you, pankajnamekar25 , for your response.

Hi Anonymous,

In addition to the response provided by pankajnamekar25 , please refer to the following code, which may help in resolving the issue:

If no effective date exists, the function will return "No applicable rate" instead of a blank value.

Selected Rate =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date]) 
VAR LatestEffectiveDate =
CALCULATE(
MAX('EmployeeRates'[Effective Date]), 
'EmployeeRates'[Effective Date] <= SelectedDate,
REMOVEFILTERS('EmployeeRates')
)
RETURN
IF(
NOT ISBLANK(LatestEffectiveDate),
CALCULATE(
MAX('EmployeeRates'[Rate]),
'EmployeeRates'[Effective Date] = LatestEffectiveDate
),
"No applicable rate" 
)

If you find our response helpful, kindly mark it as the accepted solution and provide your appreciation. This will assist other community members who may encounter similar queries.

Thank you.