topic DAX for advanced look up in DAX Commands and Tips

DAX for advanced look up

AndyDD_UK — Tue, 26 Jul 2022 19:10:35 GMT

(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 ?

Re: DAX for advanced look up

amitchandak — Wed, 27 Jul 2022 03:50:05 GMT

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-or-end-date/ba-p/1503785

Re: DAX for advanced look up

v-easonf-msft — Mon, 01 Aug 2022 09:25:28 GMT

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