Calculate average rate on days between dates

[wjshell]

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

[wjshell]

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

[v-yadongf-msft]

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.