Forum Discussion
Helper IICompare value from one table with values from another table.
we have one table with occupancy by season
Each season has unique id number
and we have this table for occupancy limits
i want to compare current occupancy from one table to these occupancies per season and based on the comparison it would give us the discount.
eg 25/5 - 3/6 is season 2.
so compare 52% occupancy with column n.2 which with 50 to 54% occupancy give us discount of -10%
its like index match in excel .
anyone can help??
5 Replies
n3ptunetried this but its not working
Suggested discount = CALCULATE(MAX(Spo_pot_20[Discount]);FILTER(Spo_pot_20;Spo_pot_20[Occupancy Limit]<=[OCC %_2020_perDate]))n3ptune
Could you provide the the some sample data, showing what are the columns in each table. I can not see what is your tables and columns from the screen shots.
Paul Zheng- n3ptune
Anonymous
this is the pricing table with discount for each occupancy limit
then there is sales table where i have the measure of current occupancy for each day, season etc.
then i have season table which is this one
pricing table and sales table have both active relationship to seasons table many to one.
so i want to create a measure called suggested special offer which will calculate a suggested discount for each season
based on the current occupancy.
for example for season 1. if current occupancy is 43% then the discount will be bar3 -5%
if current occupancy is 36% then the discount will be bar4 -10%
occupancy is simple measure
CALCULATE(DIVIDE([Total_rooms_booked];[Rooms available_2020]))the calculation is like index match at excel formulas.thank you in advance
I am sorry, could you upload a pbix sample without sensitive data, your provided information is really not explaining your model. Thank you.Paul Zheng
