DAX for advanced look up

[AndyDD_UK]

(Simplified) I have 2 tables and want to look-up the charge out rate applicable for each member of staff, based on the invoice date:

Table 1:

Col A: invoice date

Col B: employee

Col C: I want to look up their charge out rate applicable for when the job was done

Table 2:

Column A: employee

Column B:  date

Column C: rate

Table 2 Example:

Col A.....Col B.....Col C

John     15/01/2022.    100

John      03/07/2022.    120

Here you see that John's charge out rate is £100 from 15/01/2022 to 02/07/2022 where it then changes to £120

Is there a way of working this out the rate or does Table 2 need to include all dates in order to find a match ?

[v-easonf-msft · Accepted solution]

Hi, @AndyDD_UK 

Please try formula like below:

Look_up_date =
CALCULATE (
    MAX ( Table2[invoice date] ),
    FILTER (
        ALL ( Table2 ),
        Table2[invoice date] <= MAX ( Table1[invoice date] )
            && Table2[employee] = MAX ( Table1[employee] )
    )
)

Look_up_rate =
CALCULATE (
    MAX ( Table2[Rate] ),
    FILTER (
        Table2,
        Table2[employee] = MAX ( Table1[employee] )
            && Table2[invoice date] = [Look_up_date]
    )
)

Best Regards,
Community Support Team _ Eason

[amitchandak]

@AndyDD_UK , Based on what I got.

You need a new column first

end Date = minx(filter(Table, [Name] = earlier([Name]) && [Date] > earlier([date])), [Date]) -1

Then use the logic in blog or file to expand

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...