Help with Lookup Based on Date Range

[Anonymous]

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

Pay Rate = CALCULATE(MAX('Historical Monthly Rates'[Salary History Rate Amount]),
filter('Historical Monthly Rates',

'Historical Monthly Rates'[File Number] = Hours[DetailsEmployee] &&

'Historical Monthly Rates'[Rate Start] <= Hours[DetailsPeriod] &&

'Historical Monthly Rates'[Effective End] >= Hours[DetailsPeriod))

The 'Historical Monthly Rates' table looks as follows:

File Number          Rate Start              Effective End        Salary History Rate Amount

100153                    1/4/2016                1/2/2017                100

100153                    1/2/2017                3/1/2019                120

100613                    1/4/2016                1/2/2017                200

100613                    1/2/2017                1/3/2017                230

100613                    1/3/2017                5/1/2019                250

The 'Hours' table looks as follows:

DetailsEmployee          DetailsPeriod              Pay Rate(the desired calculated column results)

100153                           6/1/2016                      100

100153                           7/1/2016                      100

100153                           2/1/2017                      120

100153                           3/1/2017                      120

100613                           2/1/2016                      200

100613                           4/1/2016                      200

100613                           7/1/2016                      200

100613                           1/2/2017                      230

100613                           3/1/2017                      250

100613                           3/1/2017                      250

Thanks in advance

[Anonymous]

@Anonymous,

Please download my PBIX file to  test.

https://1drv.ms/u/s!AhsotbnGu1NogyHl3T7m0tEBQTt6


Regards,

Lydia

[Anonymous]

Lydia,

I opened the file and see that it is working correctly. I'm at a loss to why it doesn't work in my PBIX, everything seems to be the same.

[Anonymous]

@Anonymous,

Drag fields to table visuals, you will note that pay rate returns expected values.



Regards,
Lydia

[Anonymous]

Lydia,

Still showing up blank. I expected this because it is still blank in data view. I sent you a PM with my file

Thanks

[Anonymous]

@Anonymous，

Would you please share your PBIX file via Private Message?

Regards,
Lydia

[Anonymous]

@Anonymous,

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


Regards,
Lydia

[Anonymous]

Thanks for the reply, Lydia.

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.

Here are screenshots of the data tables with some of the columns blacked out due to confidentiality.

Historical Monthly Rates table

Hours table

[Zubair_Muhammad]

@Anonymous

Seems like your Rate Start and Effective End Columns are not formatted as DATE.

Change their data type to Date....I think your formula would work then

[Anonymous]

Zubair,

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.