Forum Discussion
Help needed looking up the right price bracket
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 🙂
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]
))
1 Reply
- amitchandak
Super User
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]
))