Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Calculate average rate on days between dates

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.


From the example table above, I'd like to be able to create another table or calculation that returns a row for 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  1001
02/01/231001
03/01/231001
04/01/231001
05/01/231001
06/01/231001
07/01/231001
08/01/231001
09/01/231001


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! @tamerj1 @FreemanZ @FreemanZ @amitchandak @andhiii079845 @Greg_Deckler 

4 REPLIES 4
olgad
Super User
Super User

Hi, create another custom column for the previous date of the Date To column

Date.AddDays([Date To Column],-1)
and then plug it into the function that i shared with you. 


the function creates numbers per row. 
from this table 

olgad_1-1679309101655.png

So, it shall work as you expect.

to this table:

olgad_0-1679309063995.png

 





DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
Anonymous
Not applicable

Hi @olgad Thanks very much! We are going to test this then hopefully we can Accept Solution. Thanks v much for your help. Thanks

olgad
Super User
Super User

Hi, add custom column {Number.From([Dates from])..Number.From([Dates to]) }
Expand the List, turn to date

olgad_0-1679305385597.pngolgad_1-1679305407287.png

Here is the article that explains different scenarios
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?tr...


DID I ANSWER YOUR QUESTION? PLEASE MARK MY POST AS A SOLUTION! APPRECIATE YOUR KUDO/LIKE!
PROUD TO BE A SUPER USER!
Best Stories, Interesting Cases: PowerBI Storytime Newsletter
Linkedin Profile: Linkedin
YouTube Channel: PowerBI Storytime
Anonymous
Not applicable

Thanks very much! @olgad Two quick questions:
1) Would your solution work for multiple overlapping date ranges in the first table, it's difficult to tell from your example. For 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

 This is becasue we need to be able to average the nightly rates on specific dates. 
2) These are check-in and check-out dates for stays in hotels. So therefore the rate does not apply to the last day in the range. e.g. ID 1 from the above example needs to look like this in the Output table:

DateRate ID
01/01/20231001
02/01/20231001
03/01/20231001
04/01/20231001
05/01/20231001
06/01/20231001
07/01/20231001
08/01/20231001
09/01/20231001


ie. the last date in the output table needs to be the 9th, not 10th, as rate does not apply on the check out date.

Thanks for your help!

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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