Help needed looking up the right price bracket

[siros92]

Hi awesome community, once again I find myself in need of your assistance. 

i've got one table where my sales are, and a seperate table for price agreements. Agreements are volume based and have tiers. Hence i need to lookup the correct price based on the volume in the sales table. For the costs that are fixed this is easily done by:
CALCULATE(VALUES('Price List'[Dayrate]),
FILTER('Price List',
'Price List'[Customer]='Sales'[Customer]
&& 'Sales'[Date]>='Price List'[Price valid from] && 'Sales'[Date]<='Price List'[Price valid to]))

However, the variable price based on the brackets seen below is giving me some headache. Is there any way to essentially do the same as above but as well check which bracket level the monthly sales falls into and then return the correct xxx price? I've generated some mock pricing data just to show how the list is structured.

Customer	Price valid from	Price valid to	bracket start	Bracket end	Dayrate	xxx Price
xxxx	01.08.2022	30.06.2023	0	2500	£                 10 000,00	2,5
xxxx	01.08.2022	30.06.2023	2501	12500	£                 10 000,00	2,2
xxxx	01.08.2022	30.06.2023	12501	50000	£                 10 000,00	2

In advance thanks for the help and please let me know if there is further details required 🙂

[amitchandak · Accepted solution]

@siros92 , Also put sales values in the condition

example

CALCULATE(max('Price List'[Dayrate]),
FILTER('Price List',
'Price List'[Customer]='Sales'[Customer]
&& 'Sales'[Date]>='Price List'[Price valid from] && 'Sales'[Date]<='Price List'[Price valid to]

&& 'Sales'[value]>='Price List'[bracket start] && 'Sales'[value]<='Price List'[bracket end]

))