Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
13 | |
11 | |
10 | |
7 | |
7 |
User | Count |
---|---|
13 | |
12 | |
12 | |
9 | |
8 |