cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

New Member

## 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 2
New Member

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.

Community Support

Hi @wjshell ,

``````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,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors