Measure that returns values bases upon effective date

[Anonymous]

Certainly, here’s your statement with corrected spelling and grammar:

I have a table that contains employee rates with the following columns: ID, Category, Rate, and Effective Date.

I am trying to create a measure where, if the user selects a date using a date slicer, the rates will show based on the date selected.

For example, I have effective dates of 1/1/24, 5/3/24, and 10/11/24. If a user selects 5/6/24, I want the rates to be returned with the effective date of 5/3/24. Similarly, if a user selects 10/12/24, I want the rates to be returned with the effective date of 10/11/24.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[Ashish_Mathur]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[v-pnaroju-msft]

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.

[pankajnamekar25]

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.

[Anonymous]

@pankajnamekar25  Thanks for responding. This does not work. When i select a date that is not in the employee rates table it returns blank