Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Measure that returns values bases upon effective date

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 tryi...
Need Help
pankajnamekar25's avatar
pankajnamekar25
Icon for Super User rankSuper User
1 year ago

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.