Help with Lookup Based on Date Range

Question

Trying to write an expression that looks up a value based on a date range... With the expression that I have written so far, it is returning lots of blanks&nbsp;&nbsp;Pay Rate = CALCULATE(MAX('Historical Monthly Rates'[Salary History Rate Amount]),
filter('Historical Monthly Rates',
'Historical Monthly Rates'[File Number] = Hours[DetailsEmployee] &amp;&amp;
'Historical Monthly Rates'[Rate Start] &lt;= Hours[DetailsPeriod] &amp;&amp;'Historical Monthly Rates'[Effective End] &gt;= Hours[DetailsPeriod))&nbsp;&nbsp;&nbsp;The 'Historical Monthly Rates' table looks as follows:File Number&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Rate Start&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Effective End&nbsp; &nbsp; &nbsp; &nbsp; Salary History Rate Amount100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/4/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;100100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;120100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/4/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;200100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/2/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/3/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 230100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 1/3/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;5/1/2019&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;250&nbsp;&nbsp;The 'Hours' table looks as follows:DetailsEmployee&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DetailsPeriod&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Pay Rate(the desired calculated column results)100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;6/1/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/1/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 100100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/1/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120100153&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 120100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2/1/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4/1/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7/1/2016&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 200100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1/2/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 230100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 250100613&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3/1/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 250&nbsp;Thanks in advance

anonymous · Answer

Anonymous,Your DAX formula works as expected in my scenario. Could you please share a screenshot about your result?Regards,Lydia

anonymous · Answer

Thanks for the reply, Lydia.&nbsp;I tried adding the columns to the dashboard table like you did, but when I added the Pay Rate column (the column at issue), it made everything in the table disappear.&nbsp;Here are screenshots of the data tables with some of the columns blacked out due to confidentiality.&nbsp;Historical Monthly Rates table&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hours table

zubair_muhammad · Answer

Anonymous&nbsp;Seems like your Rate Start and Effective End Columns are not formatted as DATE.&nbsp;Change their data type to Date....I think your formula would work then

anonymous · Answer

Zubair,&nbsp;You were correct in that the Rate Start and Effective End were not formatted as DATE, but were formatted as DATE/TIME. I changed them to DATE, but the formula is still not working.&nbsp;

anonymous · Answer

Anonymous,
Please download my PBIX file to&nbsp; test.https://1drv.ms/u/s!AhsotbnGu1NogyHl3T7m0tEBQTt6Regards,
Lydia

Forum Discussion

Help with Lookup Based on Date Range

9 Replies

Related content

DATE RANGE LOOKUP

Lookup for 2 Date Range from 2 Tables

Lookup help

lookup value within date range

Date lookup between ranges in PowerQuery