Forum Discussion
Calculate average rate on days between dates
Hi - i'm very much a novice on Power BI and have limited understanding of how DAX works and how to use calculated tables, but find it a fantastic tool for the parts I do understand. However, I've reached my limit in trying to calculate an average daily rate of all stays in rented accommodation
I have a table called “Rates”, which contain rows of rental rates for specific sets of dates. Each row has a column for the “Nightly rate” as well as the “dates From” and “Dates To”.
This is a basic example.
ID | Nightly rate | Dates from | Dates to | Nights |
1 | 100.00 | 01/01/2023 | 10/01/2023 | 9 |
2 | 150.00 | 12/01/2023 | 18/01/2022 | 6 |
3 | 300.00 | 02/01/2023 | 03/01/2023 | 1 |
4 | 200.00 | 07/02/2023 | 14/02/2023 | 7 |
I want to be able to calculate an average nightly rate from all the rates based on the date stayed. For example, row 1 would need transforming into a format where for each date between 01/01/2023 and 10/01/2023 carried a rate £100, and so on for all rows calculating the average rate for each individual date.
Can anyone help explain how calculation can be achieved?
Many thanks
2 Replies
v-yadongf-msftCommunity Support
Hi wjshell ,
Please try:
average rate = var sum_nights = SUM('Table'[Nights]) var sum_rate = SUMX(ALL('Table'),[Nightly rate] * [Nights]) return DIVIDE(sum_rate, sum_nights)Is this the result you want:
Best regards,
Yadong Fang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Yadong Fang
Thank you so much for your help. I really appreciate the support.
It's close, but not quite what need, probably down to my inability to ariculate it the right way :).
From the example table in the original post, I'd like to be able to create another table or calculation that returns a row each date between the 'Dates from' and Dates to' columns with the rate that applied. So using ID 1 as an example it would look lkit this.Date Rate ID 01/01/23 100 1 02/01/23 100 1 03/01/23 100 1 04/01/23 100 1 05/01/23 100 1 06/01/23 100 1 07/01/23 100 1 08/01/23 100 1 09/01/23 100 1
It would do this for all rates, and I could then link the date from my new tabe to my date table and calculate an average of all rates that applied on each date.
I hope that helps to better explain what I'm looking to achieve. Maybe the solution is not quite how I picture it, so open to any suggestions.
Thanks in advance
