My company rents out rooms at variable rates, and I'm building a report such that the sales team can quickly check "historically, what did we charge for this room in this week of the year"?
So, the user selects a room and inputs their desired week.
A text box displays the average total rate for that room in that week (total rental revenue divided by number of relevant events). The expression looks like this:
=ceil(sum({<[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}>}[Item Amount]) /count({<[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}>}[Event Desc])))
A second text box displays the average daily rate for that room in that week (total rental revenue divided by total duration of all the relevant events). The expression looks like this:
=ceil(sum({<[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}>}[Item Amount]) /ceil(sum({<[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}>}[Event Days]))), '#,##0')
A third text box shows the highest total rate charged for that room in that week. The expression looks like this:
=Max({<[Space Status]={'Contracted', 'Documented'}, [Event End Year]-={'2020', '2021'}, [Order Line Status]={'O'}, [Event End Week]={'$(vProposalWeek)'}, [Space Description]={'$(vProposalSpace)'}, [Order Department]={'01'}>}[Item Amount])
"vProposalWeek" is just a variable that stores the current selected week, and "vProposalSpace" stores the current selected space.
Where I'm stuck is that I'd like to show a fourth text box which lists the highest daily rate charged i.e. the highest total rate, divided by the duration of the event in which that rate was charged.
