Forum Discussion

siros92's avatar
siros92
Frequent Visitor
3 years ago
Solved

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.

 

CustomerPrice valid fromPrice valid tobracket startBracket endDayratexxx Price
xxxx01.08.202230.06.202302500 £                 10 000,002,5
xxxx01.08.202230.06.2023250112500 £                 10 000,002,2
xxxx01.08.202230.06.20231250150000 £                 10 000,002

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

  • 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]

     

    ))