Forum Discussion
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 | 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! tamerj1 @FreemanZ FreemanZ amitchandak andhiii079845 Greg_Deckler
4 Replies
- olgad
Resident Rockstar
Hi, add custom column {Number.From([Dates from])..Number.From([Dates to]) }
Expand the List, turn to dateHere is the article that explains different scenarios
https://www.linkedin.com/pulse/hr-reporting-generating-records-between-start-end-values-dontsova/?trackingId=bTPD6jERQXW4cwFZg%2FhJ0A%3D%3DThanks 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:Date Rate ID 01/01/2023 100 1 02/01/2023 100 1 03/01/2023 100 1 04/01/2023 100 1 05/01/2023 100 1 06/01/2023 100 1 07/01/2023 100 1 08/01/2023 100 1 09/01/2023 100 1
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!
- olgad
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 tableSo, it shall work as you expect.
to this table:
Hi olgad Thanks very much! We are going to test this then hopefully we can Accept Solution. Thanks v much for your help. Thanks
