Measure that returns values bases upon effective date

Hello Anonymous 

You can try this measure

Selected Rate =
VAR SelectedDate = SELECTEDVALUE('DateTable'[Date])
VAR LatestEffectiveDate =
CALCULATE(
MAX('EmployeeRates'[Effective Date]),
'EmployeeRates'[Effective Date] <= SelectedDate
)
RETURN
CALCULATE(
MAX('EmployeeRates'[Rate]),
'EmployeeRates'[Effective Date] = LatestEffectiveDate
)

Ensure you have a separate DateTable with a relationship to the EmployeeRates table.

Use a slicer on the DateTable'[Date] field.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

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.

Hi Mskikitv,

We have not received a response from you regarding the query and were following up to check if you have found a resolution. If you have identified a solution, we kindly request you to share it with the community, as it may be helpful to others facing a similar issue.

If you find the response helpful, please mark it as the accepted solution and provide kudos, as this will help other members with similar queries.

Thank you.

Hi Mskikitv,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

Hi,

One can use the LASTNONBLANK() function.  Share some data, explain the question and show the expected result.  Share data in a format that can be pasted in an MS Excel file.

Thankyou, Ashish_Mathur, for your response.

Hi Mskikitv,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

Hi Mskikitv,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.